1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23
  24from sqlglot.errors import ParseError
  25from sqlglot.helper import (
  26    AutoName,
  27    camel_to_snake_case,
  28    ensure_collection,
  29    seq_get,
  30    split_num_words,
  31    subclasses,
  32)
  33from sqlglot.tokens import Token
  34
  35if t.TYPE_CHECKING:
  36    from sqlglot.dialects.dialect import DialectType
  37
  38E = t.TypeVar("E", bound="Expression")
  39
  40
  41class _Expression(type):
  42    def __new__(cls, clsname, bases, attrs):
  43        klass = super().__new__(cls, clsname, bases, attrs)
  44
  45        # When an Expression class is created, its key is automatically set to be
  46        # the lowercase version of the class' name.
  47        klass.key = clsname.lower()
  48
  49        # This is so that docstrings are not inherited in pdoc
  50        klass.__doc__ = klass.__doc__ or ""
  51
  52        return klass
  53
  54
  55class Expression(metaclass=_Expression):
  56    """
  57    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  58    context, such as its child expressions, their names (arg keys), and whether a given child expression
  59    is optional or not.
  60
  61    Attributes:
  62        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  63            and representing expressions as strings.
  64        arg_types: determines what arguments (child nodes) are supported by an expression. It
  65            maps arg keys to booleans that indicate whether the corresponding args are optional.
  66
  67    Example:
  68        >>> class Foo(Expression):
  69        ...     arg_types = {"this": True, "expression": False}
  70
  71        The above definition informs us that Foo is an Expression that requires an argument called
  72        "this" and may also optionally receive an argument called "expression".
  73
  74    Args:
  75        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  76        parent: a reference to the parent expression (or None, in case of root expressions).
  77        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  78            uses to refer to it.
  79        comments: a list of comments that are associated with a given expression. This is used in
  80            order to preserve comments when transpiling SQL code.
  81        _type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  82            optimizer, in order to enable some transformations that require type information.
  83    """
  84
  85    key = "expression"
  86    arg_types = {"this": True}
  87    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta")
  88
  89    def __init__(self, **args: t.Any):
  90        self.args: t.Dict[str, t.Any] = args
  91        self.parent: t.Optional[Expression] = None
  92        self.arg_key: t.Optional[str] = None
  93        self.comments: t.Optional[t.List[str]] = None
  94        self._type: t.Optional[DataType] = None
  95        self._meta: t.Optional[t.Dict[str, t.Any]] = None
  96
  97        for arg_key, value in self.args.items():
  98            self._set_parent(arg_key, value)
  99
 100    def __eq__(self, other) -> bool:
 101        return type(self) is type(other) and _norm_args(self) == _norm_args(other)
 102
 103    def __hash__(self) -> int:
 104        return hash(
 105            (
 106                self.key,
 107                tuple(
 108                    (k, tuple(v) if isinstance(v, list) else v) for k, v in _norm_args(self).items()
 109                ),
 110            )
 111        )
 112
 113    @property
 114    def this(self):
 115        """
 116        Retrieves the argument with key "this".
 117        """
 118        return self.args.get("this")
 119
 120    @property
 121    def expression(self):
 122        """
 123        Retrieves the argument with key "expression".
 124        """
 125        return self.args.get("expression")
 126
 127    @property
 128    def expressions(self):
 129        """
 130        Retrieves the argument with key "expressions".
 131        """
 132        return self.args.get("expressions") or []
 133
 134    def text(self, key) -> str:
 135        """
 136        Returns a textual representation of the argument corresponding to "key". This can only be used
 137        for args that are strings or leaf Expression instances, such as identifiers and literals.
 138        """
 139        field = self.args.get(key)
 140        if isinstance(field, str):
 141            return field
 142        if isinstance(field, (Identifier, Literal, Var)):
 143            return field.this
 144        if isinstance(field, (Star, Null)):
 145            return field.name
 146        return ""
 147
 148    @property
 149    def is_string(self) -> bool:
 150        """
 151        Checks whether a Literal expression is a string.
 152        """
 153        return isinstance(self, Literal) and self.args["is_string"]
 154
 155    @property
 156    def is_number(self) -> bool:
 157        """
 158        Checks whether a Literal expression is a number.
 159        """
 160        return isinstance(self, Literal) and not self.args["is_string"]
 161
 162    @property
 163    def is_int(self) -> bool:
 164        """
 165        Checks whether a Literal expression is an integer.
 166        """
 167        if self.is_number:
 168            try:
 169                int(self.name)
 170                return True
 171            except ValueError:
 172                pass
 173        return False
 174
 175    @property
 176    def is_star(self) -> bool:
 177        """Checks whether an expression is a star."""
 178        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 179
 180    @property
 181    def alias(self) -> str:
 182        """
 183        Returns the alias of the expression, or an empty string if it's not aliased.
 184        """
 185        if isinstance(self.args.get("alias"), TableAlias):
 186            return self.args["alias"].name
 187        return self.text("alias")
 188
 189    @property
 190    def name(self) -> str:
 191        return self.text("this")
 192
 193    @property
 194    def alias_or_name(self):
 195        return self.alias or self.name
 196
 197    @property
 198    def output_name(self):
 199        """
 200        Name of the output column if this expression is a selection.
 201
 202        If the Expression has no output name, an empty string is returned.
 203
 204        Example:
 205            >>> from sqlglot import parse_one
 206            >>> parse_one("SELECT a").expressions[0].output_name
 207            'a'
 208            >>> parse_one("SELECT b AS c").expressions[0].output_name
 209            'c'
 210            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 211            ''
 212        """
 213        return ""
 214
 215    @property
 216    def type(self) -> t.Optional[DataType]:
 217        return self._type
 218
 219    @type.setter
 220    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 221        if dtype and not isinstance(dtype, DataType):
 222            dtype = DataType.build(dtype)
 223        self._type = dtype  # type: ignore
 224
 225    @property
 226    def meta(self) -> t.Dict[str, t.Any]:
 227        if self._meta is None:
 228            self._meta = {}
 229        return self._meta
 230
 231    def __deepcopy__(self, memo):
 232        copy = self.__class__(**deepcopy(self.args))
 233        if self.comments is not None:
 234            copy.comments = deepcopy(self.comments)
 235
 236        if self._type is not None:
 237            copy._type = self._type.copy()
 238
 239        if self._meta is not None:
 240            copy._meta = deepcopy(self._meta)
 241
 242        return copy
 243
 244    def copy(self):
 245        """
 246        Returns a deep copy of the expression.
 247        """
 248        new = deepcopy(self)
 249        new.parent = self.parent
 250        for item, parent, _ in new.bfs():
 251            if isinstance(item, Expression) and parent:
 252                item.parent = parent
 253        return new
 254
 255    def append(self, arg_key, value):
 256        """
 257        Appends value to arg_key if it's a list or sets it as a new list.
 258
 259        Args:
 260            arg_key (str): name of the list expression arg
 261            value (Any): value to append to the list
 262        """
 263        if not isinstance(self.args.get(arg_key), list):
 264            self.args[arg_key] = []
 265        self.args[arg_key].append(value)
 266        self._set_parent(arg_key, value)
 267
 268    def set(self, arg_key, value):
 269        """
 270        Sets `arg_key` to `value`.
 271
 272        Args:
 273            arg_key (str): name of the expression arg.
 274            value: value to set the arg to.
 275        """
 276        self.args[arg_key] = value
 277        self._set_parent(arg_key, value)
 278
 279    def _set_parent(self, arg_key, value):
 280        if isinstance(value, Expression):
 281            value.parent = self
 282            value.arg_key = arg_key
 283        elif isinstance(value, list):
 284            for v in value:
 285                if isinstance(v, Expression):
 286                    v.parent = self
 287                    v.arg_key = arg_key
 288
 289    @property
 290    def depth(self):
 291        """
 292        Returns the depth of this tree.
 293        """
 294        if self.parent:
 295            return self.parent.depth + 1
 296        return 0
 297
 298    def find(self, *expression_types: t.Type[E], bfs=True) -> E | None:
 299        """
 300        Returns the first node in this tree which matches at least one of
 301        the specified types.
 302
 303        Args:
 304            expression_types (type): the expression type(s) to match.
 305
 306        Returns:
 307            The node which matches the criteria or None if no such node was found.
 308        """
 309        return next(self.find_all(*expression_types, bfs=bfs), None)
 310
 311    def find_all(self, *expression_types: t.Type[E], bfs=True) -> t.Iterator[E]:
 312        """
 313        Returns a generator object which visits all nodes in this tree and only
 314        yields those that match at least one of the specified expression types.
 315
 316        Args:
 317            expression_types (type): the expression type(s) to match.
 318
 319        Returns:
 320            The generator object.
 321        """
 322        for expression, _, _ in self.walk(bfs=bfs):
 323            if isinstance(expression, expression_types):
 324                yield expression
 325
 326    def find_ancestor(self, *expression_types: t.Type[E]) -> E | None:
 327        """
 328        Returns a nearest parent matching expression_types.
 329
 330        Args:
 331            expression_types (type): the expression type(s) to match.
 332
 333        Returns:
 334            The parent node.
 335        """
 336        ancestor = self.parent
 337        while ancestor and not isinstance(ancestor, expression_types):
 338            ancestor = ancestor.parent
 339        # ignore type because mypy doesn't know that we're checking type in the loop
 340        return ancestor  # type: ignore[return-value]
 341
 342    @property
 343    def parent_select(self):
 344        """
 345        Returns the parent select statement.
 346        """
 347        return self.find_ancestor(Select)
 348
 349    def root(self) -> Expression:
 350        """
 351        Returns the root expression of this tree.
 352        """
 353        expression = self
 354        while expression.parent:
 355            expression = expression.parent
 356        return expression
 357
 358    def walk(self, bfs=True, prune=None):
 359        """
 360        Returns a generator object which visits all nodes in this tree.
 361
 362        Args:
 363            bfs (bool): if set to True the BFS traversal order will be applied,
 364                otherwise the DFS traversal will be used instead.
 365            prune ((node, parent, arg_key) -> bool): callable that returns True if
 366                the generator should stop traversing this branch of the tree.
 367
 368        Returns:
 369            the generator object.
 370        """
 371        if bfs:
 372            yield from self.bfs(prune=prune)
 373        else:
 374            yield from self.dfs(prune=prune)
 375
 376    def dfs(self, parent=None, key=None, prune=None):
 377        """
 378        Returns a generator object which visits all nodes in this tree in
 379        the DFS (Depth-first) order.
 380
 381        Returns:
 382            The generator object.
 383        """
 384        parent = parent or self.parent
 385        yield self, parent, key
 386        if prune and prune(self, parent, key):
 387            return
 388
 389        for k, v in self.args.items():
 390            for node in ensure_collection(v):
 391                if isinstance(node, Expression):
 392                    yield from node.dfs(self, k, prune)
 393
 394    def bfs(self, prune=None):
 395        """
 396        Returns a generator object which visits all nodes in this tree in
 397        the BFS (Breadth-first) order.
 398
 399        Returns:
 400            The generator object.
 401        """
 402        queue = deque([(self, self.parent, None)])
 403
 404        while queue:
 405            item, parent, key = queue.popleft()
 406
 407            yield item, parent, key
 408            if prune and prune(item, parent, key):
 409                continue
 410
 411            if isinstance(item, Expression):
 412                for k, v in item.args.items():
 413                    for node in ensure_collection(v):
 414                        if isinstance(node, Expression):
 415                            queue.append((node, item, k))
 416
 417    def unnest(self):
 418        """
 419        Returns the first non parenthesis child or self.
 420        """
 421        expression = self
 422        while isinstance(expression, Paren):
 423            expression = expression.this
 424        return expression
 425
 426    def unalias(self):
 427        """
 428        Returns the inner expression if this is an Alias.
 429        """
 430        if isinstance(self, Alias):
 431            return self.this
 432        return self
 433
 434    def unnest_operands(self):
 435        """
 436        Returns unnested operands as a tuple.
 437        """
 438        return tuple(arg.unnest() for arg in self.args.values() if arg)
 439
 440    def flatten(self, unnest=True):
 441        """
 442        Returns a generator which yields child nodes who's parents are the same class.
 443
 444        A AND B AND C -> [A, B, C]
 445        """
 446        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not isinstance(n, self.__class__)):
 447            if not isinstance(node, self.__class__):
 448                yield node.unnest() if unnest else node
 449
 450    def __str__(self):
 451        return self.sql()
 452
 453    def __repr__(self):
 454        return self._to_s()
 455
 456    def sql(self, dialect: DialectType = None, **opts) -> str:
 457        """
 458        Returns SQL string representation of this tree.
 459
 460        Args:
 461            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 462            opts: other `sqlglot.generator.Generator` options.
 463
 464        Returns:
 465            The SQL string.
 466        """
 467        from sqlglot.dialects import Dialect
 468
 469        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 470
 471    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 472        indent = "" if not level else "\n"
 473        indent += "".join(["  "] * level)
 474        left = f"({self.key.upper()} "
 475
 476        args: t.Dict[str, t.Any] = {
 477            k: ", ".join(
 478                v._to_s(hide_missing=hide_missing, level=level + 1)
 479                if hasattr(v, "_to_s")
 480                else str(v)
 481                for v in ensure_collection(vs)
 482                if v is not None
 483            )
 484            for k, vs in self.args.items()
 485        }
 486        args["comments"] = self.comments
 487        args["type"] = self.type
 488        args = {k: v for k, v in args.items() if v or not hide_missing}
 489
 490        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 491        right += ")"
 492
 493        return indent + left + right
 494
 495    def transform(self, fun, *args, copy=True, **kwargs):
 496        """
 497        Recursively visits all tree nodes (excluding already transformed ones)
 498        and applies the given transformation function to each node.
 499
 500        Args:
 501            fun (function): a function which takes a node as an argument and returns a
 502                new transformed node or the same node without modifications. If the function
 503                returns None, then the corresponding node will be removed from the syntax tree.
 504            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 505                modified in place.
 506
 507        Returns:
 508            The transformed tree.
 509        """
 510        node = self.copy() if copy else self
 511        new_node = fun(node, *args, **kwargs)
 512
 513        if new_node is None or not isinstance(new_node, Expression):
 514            return new_node
 515        if new_node is not node:
 516            new_node.parent = node.parent
 517            return new_node
 518
 519        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 520        return new_node
 521
 522    def replace(self, expression):
 523        """
 524        Swap out this expression with a new expression.
 525
 526        For example::
 527
 528            >>> tree = Select().select("x").from_("tbl")
 529            >>> tree.find(Column).replace(Column(this="y"))
 530            (COLUMN this: y)
 531            >>> tree.sql()
 532            'SELECT y FROM tbl'
 533
 534        Args:
 535            expression (Expression|None): new node
 536
 537        Returns:
 538            The new expression or expressions.
 539        """
 540        if not self.parent:
 541            return expression
 542
 543        parent = self.parent
 544        self.parent = None
 545
 546        replace_children(parent, lambda child: expression if child is self else child)
 547        return expression
 548
 549    def pop(self):
 550        """
 551        Remove this expression from its AST.
 552        """
 553        self.replace(None)
 554
 555    def assert_is(self, type_):
 556        """
 557        Assert that this `Expression` is an instance of `type_`.
 558
 559        If it is NOT an instance of `type_`, this raises an assertion error.
 560        Otherwise, this returns this expression.
 561
 562        Examples:
 563            This is useful for type security in chained expressions:
 564
 565            >>> import sqlglot
 566            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 567            'SELECT x, z FROM y'
 568        """
 569        assert isinstance(self, type_)
 570        return self
 571
 572    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 573        """
 574        Checks if this expression is valid (e.g. all mandatory args are set).
 575
 576        Args:
 577            args: a sequence of values that were used to instantiate a Func expression. This is used
 578                to check that the provided arguments don't exceed the function argument limit.
 579
 580        Returns:
 581            A list of error messages for all possible errors that were found.
 582        """
 583        errors: t.List[str] = []
 584
 585        for k in self.args:
 586            if k not in self.arg_types:
 587                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 588        for k, mandatory in self.arg_types.items():
 589            v = self.args.get(k)
 590            if mandatory and (v is None or (isinstance(v, list) and not v)):
 591                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 592
 593        if (
 594            args
 595            and isinstance(self, Func)
 596            and len(args) > len(self.arg_types)
 597            and not self.is_var_len_args
 598        ):
 599            errors.append(
 600                f"The number of provided arguments ({len(args)}) is greater than "
 601                f"the maximum number of supported arguments ({len(self.arg_types)})"
 602            )
 603
 604        return errors
 605
 606    def dump(self):
 607        """
 608        Dump this Expression to a JSON-serializable dict.
 609        """
 610        from sqlglot.serde import dump
 611
 612        return dump(self)
 613
 614    @classmethod
 615    def load(cls, obj):
 616        """
 617        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 618        """
 619        from sqlglot.serde import load
 620
 621        return load(obj)
 622
 623
 624IntoType = t.Union[
 625    str,
 626    t.Type[Expression],
 627    t.Collection[t.Union[str, t.Type[Expression]]],
 628]
 629
 630
 631class Condition(Expression):
 632    def and_(self, *expressions, dialect=None, **opts):
 633        """
 634        AND this condition with one or multiple expressions.
 635
 636        Example:
 637            >>> condition("x=1").and_("y=1").sql()
 638            'x = 1 AND y = 1'
 639
 640        Args:
 641            *expressions (str | Expression): the SQL code strings to parse.
 642                If an `Expression` instance is passed, it will be used as-is.
 643            dialect (str): the dialect used to parse the input expression.
 644            opts (kwargs): other options to use to parse the input expressions.
 645
 646        Returns:
 647            And: the new condition.
 648        """
 649        return and_(self, *expressions, dialect=dialect, **opts)
 650
 651    def or_(self, *expressions, dialect=None, **opts):
 652        """
 653        OR this condition with one or multiple expressions.
 654
 655        Example:
 656            >>> condition("x=1").or_("y=1").sql()
 657            'x = 1 OR y = 1'
 658
 659        Args:
 660            *expressions (str | Expression): the SQL code strings to parse.
 661                If an `Expression` instance is passed, it will be used as-is.
 662            dialect (str): the dialect used to parse the input expression.
 663            opts (kwargs): other options to use to parse the input expressions.
 664
 665        Returns:
 666            Or: the new condition.
 667        """
 668        return or_(self, *expressions, dialect=dialect, **opts)
 669
 670    def not_(self):
 671        """
 672        Wrap this condition with NOT.
 673
 674        Example:
 675            >>> condition("x=1").not_().sql()
 676            'NOT x = 1'
 677
 678        Returns:
 679            Not: the new condition.
 680        """
 681        return not_(self)
 682
 683
 684class Predicate(Condition):
 685    """Relationships like x = y, x > 1, x >= y."""
 686
 687
 688class DerivedTable(Expression):
 689    @property
 690    def alias_column_names(self):
 691        table_alias = self.args.get("alias")
 692        if not table_alias:
 693            return []
 694        column_list = table_alias.assert_is(TableAlias).args.get("columns") or []
 695        return [c.name for c in column_list]
 696
 697    @property
 698    def selects(self):
 699        alias = self.args.get("alias")
 700
 701        if alias:
 702            return alias.columns
 703        return []
 704
 705    @property
 706    def named_selects(self):
 707        return [select.output_name for select in self.selects]
 708
 709
 710class Unionable(Expression):
 711    def union(self, expression, distinct=True, dialect=None, **opts):
 712        """
 713        Builds a UNION expression.
 714
 715        Example:
 716            >>> import sqlglot
 717            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 718            'SELECT * FROM foo UNION SELECT * FROM bla'
 719
 720        Args:
 721            expression (str | Expression): the SQL code string.
 722                If an `Expression` instance is passed, it will be used as-is.
 723            distinct (bool): set the DISTINCT flag if and only if this is true.
 724            dialect (str): the dialect used to parse the input expression.
 725            opts (kwargs): other options to use to parse the input expressions.
 726        Returns:
 727            Union: the Union expression.
 728        """
 729        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 730
 731    def intersect(self, expression, distinct=True, dialect=None, **opts):
 732        """
 733        Builds an INTERSECT expression.
 734
 735        Example:
 736            >>> import sqlglot
 737            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 738            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 739
 740        Args:
 741            expression (str | Expression): the SQL code string.
 742                If an `Expression` instance is passed, it will be used as-is.
 743            distinct (bool): set the DISTINCT flag if and only if this is true.
 744            dialect (str): the dialect used to parse the input expression.
 745            opts (kwargs): other options to use to parse the input expressions.
 746        Returns:
 747            Intersect: the Intersect expression
 748        """
 749        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 750
 751    def except_(self, expression, distinct=True, dialect=None, **opts):
 752        """
 753        Builds an EXCEPT expression.
 754
 755        Example:
 756            >>> import sqlglot
 757            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 758            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 759
 760        Args:
 761            expression (str | Expression): the SQL code string.
 762                If an `Expression` instance is passed, it will be used as-is.
 763            distinct (bool): set the DISTINCT flag if and only if this is true.
 764            dialect (str): the dialect used to parse the input expression.
 765            opts (kwargs): other options to use to parse the input expressions.
 766        Returns:
 767            Except: the Except expression
 768        """
 769        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 770
 771
 772class UDTF(DerivedTable, Unionable):
 773    pass
 774
 775
 776class Cache(Expression):
 777    arg_types = {
 778        "with": False,
 779        "this": True,
 780        "lazy": False,
 781        "options": False,
 782        "expression": False,
 783    }
 784
 785
 786class Uncache(Expression):
 787    arg_types = {"this": True, "exists": False}
 788
 789
 790class Create(Expression):
 791    arg_types = {
 792        "with": False,
 793        "this": True,
 794        "kind": True,
 795        "expression": False,
 796        "exists": False,
 797        "properties": False,
 798        "replace": False,
 799        "unique": False,
 800        "volatile": False,
 801        "indexes": False,
 802        "no_schema_binding": False,
 803        "begin": False,
 804    }
 805
 806
 807class Describe(Expression):
 808    arg_types = {"this": True, "kind": False}
 809
 810
 811class Set(Expression):
 812    arg_types = {"expressions": True}
 813
 814
 815class SetItem(Expression):
 816    arg_types = {
 817        "this": False,
 818        "expressions": False,
 819        "kind": False,
 820        "collate": False,  # MySQL SET NAMES statement
 821        "global": False,
 822    }
 823
 824
 825class Show(Expression):
 826    arg_types = {
 827        "this": True,
 828        "target": False,
 829        "offset": False,
 830        "limit": False,
 831        "like": False,
 832        "where": False,
 833        "db": False,
 834        "full": False,
 835        "mutex": False,
 836        "query": False,
 837        "channel": False,
 838        "global": False,
 839        "log": False,
 840        "position": False,
 841        "types": False,
 842    }
 843
 844
 845class UserDefinedFunction(Expression):
 846    arg_types = {"this": True, "expressions": False, "wrapped": False}
 847
 848
 849class CharacterSet(Expression):
 850    arg_types = {"this": True, "default": False}
 851
 852
 853class With(Expression):
 854    arg_types = {"expressions": True, "recursive": False}
 855
 856    @property
 857    def recursive(self) -> bool:
 858        return bool(self.args.get("recursive"))
 859
 860
 861class WithinGroup(Expression):
 862    arg_types = {"this": True, "expression": False}
 863
 864
 865class CTE(DerivedTable):
 866    arg_types = {"this": True, "alias": True}
 867
 868
 869class TableAlias(Expression):
 870    arg_types = {"this": False, "columns": False}
 871
 872    @property
 873    def columns(self):
 874        return self.args.get("columns") or []
 875
 876
 877class BitString(Condition):
 878    pass
 879
 880
 881class HexString(Condition):
 882    pass
 883
 884
 885class ByteString(Condition):
 886    pass
 887
 888
 889class Column(Condition):
 890    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
 891
 892    @property
 893    def table(self) -> str:
 894        return self.text("table")
 895
 896    @property
 897    def db(self) -> str:
 898        return self.text("db")
 899
 900    @property
 901    def catalog(self) -> str:
 902        return self.text("catalog")
 903
 904    @property
 905    def output_name(self) -> str:
 906        return self.name
 907
 908
 909class ColumnDef(Expression):
 910    arg_types = {
 911        "this": True,
 912        "kind": False,
 913        "constraints": False,
 914        "exists": False,
 915    }
 916
 917
 918class AlterColumn(Expression):
 919    arg_types = {
 920        "this": True,
 921        "dtype": False,
 922        "collate": False,
 923        "using": False,
 924        "default": False,
 925        "drop": False,
 926    }
 927
 928
 929class RenameTable(Expression):
 930    pass
 931
 932
 933class SetTag(Expression):
 934    arg_types = {"expressions": True, "unset": False}
 935
 936
 937class Comment(Expression):
 938    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
 939
 940
 941class ColumnConstraint(Expression):
 942    arg_types = {"this": False, "kind": True}
 943
 944
 945class ColumnConstraintKind(Expression):
 946    pass
 947
 948
 949class AutoIncrementColumnConstraint(ColumnConstraintKind):
 950    pass
 951
 952
 953class CaseSpecificColumnConstraint(ColumnConstraintKind):
 954    arg_types = {"not_": True}
 955
 956
 957class CharacterSetColumnConstraint(ColumnConstraintKind):
 958    arg_types = {"this": True}
 959
 960
 961class CheckColumnConstraint(ColumnConstraintKind):
 962    pass
 963
 964
 965class CollateColumnConstraint(ColumnConstraintKind):
 966    pass
 967
 968
 969class CommentColumnConstraint(ColumnConstraintKind):
 970    pass
 971
 972
 973class CompressColumnConstraint(ColumnConstraintKind):
 974    pass
 975
 976
 977class DateFormatColumnConstraint(ColumnConstraintKind):
 978    arg_types = {"this": True}
 979
 980
 981class DefaultColumnConstraint(ColumnConstraintKind):
 982    pass
 983
 984
 985class EncodeColumnConstraint(ColumnConstraintKind):
 986    pass
 987
 988
 989class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
 990    # this: True -> ALWAYS, this: False -> BY DEFAULT
 991    arg_types = {
 992        "this": False,
 993        "start": False,
 994        "increment": False,
 995        "minvalue": False,
 996        "maxvalue": False,
 997        "cycle": False,
 998    }
 999
1000
1001class InlineLengthColumnConstraint(ColumnConstraintKind):
1002    pass
1003
1004
1005class NotNullColumnConstraint(ColumnConstraintKind):
1006    arg_types = {"allow_null": False}
1007
1008
1009class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1010    arg_types = {"desc": False}
1011
1012
1013class TitleColumnConstraint(ColumnConstraintKind):
1014    pass
1015
1016
1017class UniqueColumnConstraint(ColumnConstraintKind):
1018    arg_types: t.Dict[str, t.Any] = {}
1019
1020
1021class UppercaseColumnConstraint(ColumnConstraintKind):
1022    arg_types: t.Dict[str, t.Any] = {}
1023
1024
1025class PathColumnConstraint(ColumnConstraintKind):
1026    pass
1027
1028
1029class Constraint(Expression):
1030    arg_types = {"this": True, "expressions": True}
1031
1032
1033class Delete(Expression):
1034    arg_types = {"with": False, "this": False, "using": False, "where": False}
1035
1036
1037class Drop(Expression):
1038    arg_types = {
1039        "this": False,
1040        "kind": False,
1041        "exists": False,
1042        "temporary": False,
1043        "materialized": False,
1044        "cascade": False,
1045    }
1046
1047
1048class Filter(Expression):
1049    arg_types = {"this": True, "expression": True}
1050
1051
1052class Check(Expression):
1053    pass
1054
1055
1056class Directory(Expression):
1057    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1058    arg_types = {"this": True, "local": False, "row_format": False}
1059
1060
1061class ForeignKey(Expression):
1062    arg_types = {
1063        "expressions": True,
1064        "reference": False,
1065        "delete": False,
1066        "update": False,
1067    }
1068
1069
1070class PrimaryKey(Expression):
1071    arg_types = {"expressions": True, "options": False}
1072
1073
1074class Unique(Expression):
1075    arg_types = {"expressions": True}
1076
1077
1078# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1079# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1080class Into(Expression):
1081    arg_types = {"this": True, "temporary": False, "unlogged": False}
1082
1083
1084class From(Expression):
1085    arg_types = {"expressions": True}
1086
1087
1088class Having(Expression):
1089    pass
1090
1091
1092class Hint(Expression):
1093    arg_types = {"expressions": True}
1094
1095
1096class JoinHint(Expression):
1097    arg_types = {"this": True, "expressions": True}
1098
1099
1100class Identifier(Expression):
1101    arg_types = {"this": True, "quoted": False}
1102
1103    @property
1104    def quoted(self):
1105        return bool(self.args.get("quoted"))
1106
1107    def __eq__(self, other):
1108        return isinstance(other, self.__class__) and _norm_arg(self.this) == _norm_arg(other.this)
1109
1110    def __hash__(self):
1111        return hash((self.key, self.this.lower()))
1112
1113    @property
1114    def output_name(self):
1115        return self.name
1116
1117
1118class Index(Expression):
1119    arg_types = {
1120        "this": False,
1121        "table": False,
1122        "where": False,
1123        "columns": False,
1124        "unique": False,
1125        "primary": False,
1126        "amp": False,  # teradata
1127    }
1128
1129
1130class Insert(Expression):
1131    arg_types = {
1132        "with": False,
1133        "this": True,
1134        "expression": False,
1135        "overwrite": False,
1136        "exists": False,
1137        "partition": False,
1138        "alternative": False,
1139    }
1140
1141
1142# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1143class Introducer(Expression):
1144    arg_types = {"this": True, "expression": True}
1145
1146
1147# national char, like n'utf8'
1148class National(Expression):
1149    pass
1150
1151
1152class LoadData(Expression):
1153    arg_types = {
1154        "this": True,
1155        "local": False,
1156        "overwrite": False,
1157        "inpath": True,
1158        "partition": False,
1159        "input_format": False,
1160        "serde": False,
1161    }
1162
1163
1164class Partition(Expression):
1165    arg_types = {"expressions": True}
1166
1167
1168class Fetch(Expression):
1169    arg_types = {"direction": False, "count": False}
1170
1171
1172class Group(Expression):
1173    arg_types = {
1174        "expressions": False,
1175        "grouping_sets": False,
1176        "cube": False,
1177        "rollup": False,
1178    }
1179
1180
1181class Lambda(Expression):
1182    arg_types = {"this": True, "expressions": True}
1183
1184
1185class Limit(Expression):
1186    arg_types = {"this": False, "expression": True}
1187
1188
1189class Literal(Condition):
1190    arg_types = {"this": True, "is_string": True}
1191
1192    def __eq__(self, other):
1193        return (
1194            isinstance(other, Literal)
1195            and self.this == other.this
1196            and self.args["is_string"] == other.args["is_string"]
1197        )
1198
1199    def __hash__(self):
1200        return hash((self.key, self.this, self.args["is_string"]))
1201
1202    @classmethod
1203    def number(cls, number) -> Literal:
1204        return cls(this=str(number), is_string=False)
1205
1206    @classmethod
1207    def string(cls, string) -> Literal:
1208        return cls(this=str(string), is_string=True)
1209
1210    @property
1211    def output_name(self):
1212        return self.name
1213
1214
1215class Join(Expression):
1216    arg_types = {
1217        "this": True,
1218        "on": False,
1219        "side": False,
1220        "kind": False,
1221        "using": False,
1222        "natural": False,
1223    }
1224
1225    @property
1226    def kind(self):
1227        return self.text("kind").upper()
1228
1229    @property
1230    def side(self):
1231        return self.text("side").upper()
1232
1233    @property
1234    def alias_or_name(self):
1235        return self.this.alias_or_name
1236
1237    def on(self, *expressions, append=True, dialect=None, copy=True, **opts):
1238        """
1239        Append to or set the ON expressions.
1240
1241        Example:
1242            >>> import sqlglot
1243            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1244            'JOIN x ON y = 1'
1245
1246        Args:
1247            *expressions (str | Expression): the SQL code strings to parse.
1248                If an `Expression` instance is passed, it will be used as-is.
1249                Multiple expressions are combined with an AND operator.
1250            append (bool): if `True`, AND the new expressions to any existing expression.
1251                Otherwise, this resets the expression.
1252            dialect (str): the dialect used to parse the input expressions.
1253            copy (bool): if `False`, modify this expression instance in-place.
1254            opts (kwargs): other options to use to parse the input expressions.
1255
1256        Returns:
1257            Join: the modified join expression.
1258        """
1259        join = _apply_conjunction_builder(
1260            *expressions,
1261            instance=self,
1262            arg="on",
1263            append=append,
1264            dialect=dialect,
1265            copy=copy,
1266            **opts,
1267        )
1268
1269        if join.kind == "CROSS":
1270            join.set("kind", None)
1271
1272        return join
1273
1274    def using(self, *expressions, append=True, dialect=None, copy=True, **opts):
1275        """
1276        Append to or set the USING expressions.
1277
1278        Example:
1279            >>> import sqlglot
1280            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1281            'JOIN x USING (foo, bla)'
1282
1283        Args:
1284            *expressions (str | Expression): the SQL code strings to parse.
1285                If an `Expression` instance is passed, it will be used as-is.
1286            append (bool): if `True`, concatenate the new expressions to the existing "using" list.
1287                Otherwise, this resets the expression.
1288            dialect (str): the dialect used to parse the input expressions.
1289            copy (bool): if `False`, modify this expression instance in-place.
1290            opts (kwargs): other options to use to parse the input expressions.
1291
1292        Returns:
1293            Join: the modified join expression.
1294        """
1295        join = _apply_list_builder(
1296            *expressions,
1297            instance=self,
1298            arg="using",
1299            append=append,
1300            dialect=dialect,
1301            copy=copy,
1302            **opts,
1303        )
1304
1305        if join.kind == "CROSS":
1306            join.set("kind", None)
1307
1308        return join
1309
1310
1311class Lateral(UDTF):
1312    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1313
1314
1315class MatchRecognize(Expression):
1316    arg_types = {
1317        "partition_by": False,
1318        "order": False,
1319        "measures": False,
1320        "rows": False,
1321        "after": False,
1322        "pattern": False,
1323        "define": False,
1324    }
1325
1326
1327# Clickhouse FROM FINAL modifier
1328# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1329class Final(Expression):
1330    pass
1331
1332
1333class Offset(Expression):
1334    arg_types = {"this": False, "expression": True}
1335
1336
1337class Order(Expression):
1338    arg_types = {"this": False, "expressions": True}
1339
1340
1341# hive specific sorts
1342# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1343class Cluster(Order):
1344    pass
1345
1346
1347class Distribute(Order):
1348    pass
1349
1350
1351class Sort(Order):
1352    pass
1353
1354
1355class Ordered(Expression):
1356    arg_types = {"this": True, "desc": True, "nulls_first": True}
1357
1358
1359class Property(Expression):
1360    arg_types = {"this": True, "value": True}
1361
1362
1363class AfterJournalProperty(Property):
1364    arg_types = {"no": True, "dual": False, "local": False}
1365
1366
1367class AlgorithmProperty(Property):
1368    arg_types = {"this": True}
1369
1370
1371class AutoIncrementProperty(Property):
1372    arg_types = {"this": True}
1373
1374
1375class BlockCompressionProperty(Property):
1376    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1377
1378
1379class CharacterSetProperty(Property):
1380    arg_types = {"this": True, "default": True}
1381
1382
1383class ChecksumProperty(Property):
1384    arg_types = {"on": False, "default": False}
1385
1386
1387class CollateProperty(Property):
1388    arg_types = {"this": True}
1389
1390
1391class DataBlocksizeProperty(Property):
1392    arg_types = {"size": False, "units": False, "min": False, "default": False}
1393
1394
1395class DefinerProperty(Property):
1396    arg_types = {"this": True}
1397
1398
1399class DistKeyProperty(Property):
1400    arg_types = {"this": True}
1401
1402
1403class DistStyleProperty(Property):
1404    arg_types = {"this": True}
1405
1406
1407class EngineProperty(Property):
1408    arg_types = {"this": True}
1409
1410
1411class ExecuteAsProperty(Property):
1412    arg_types = {"this": True}
1413
1414
1415class ExternalProperty(Property):
1416    arg_types = {"this": False}
1417
1418
1419class FallbackProperty(Property):
1420    arg_types = {"no": True, "protection": False}
1421
1422
1423class FileFormatProperty(Property):
1424    arg_types = {"this": True}
1425
1426
1427class FreespaceProperty(Property):
1428    arg_types = {"this": True, "percent": False}
1429
1430
1431class IsolatedLoadingProperty(Property):
1432    arg_types = {
1433        "no": True,
1434        "concurrent": True,
1435        "for_all": True,
1436        "for_insert": True,
1437        "for_none": True,
1438    }
1439
1440
1441class JournalProperty(Property):
1442    arg_types = {"no": True, "dual": False, "before": False}
1443
1444
1445class LanguageProperty(Property):
1446    arg_types = {"this": True}
1447
1448
1449class LikeProperty(Property):
1450    arg_types = {"this": True, "expressions": False}
1451
1452
1453class LocationProperty(Property):
1454    arg_types = {"this": True}
1455
1456
1457class LockingProperty(Property):
1458    arg_types = {
1459        "this": False,
1460        "kind": True,
1461        "for_or_in": True,
1462        "lock_type": True,
1463        "override": False,
1464    }
1465
1466
1467class LogProperty(Property):
1468    arg_types = {"no": True}
1469
1470
1471class MaterializedProperty(Property):
1472    arg_types = {"this": False}
1473
1474
1475class MergeBlockRatioProperty(Property):
1476    arg_types = {"this": False, "no": False, "default": False, "percent": False}
1477
1478
1479class NoPrimaryIndexProperty(Property):
1480    arg_types = {"this": False}
1481
1482
1483class OnCommitProperty(Property):
1484    arg_type = {"this": False}
1485
1486
1487class PartitionedByProperty(Property):
1488    arg_types = {"this": True}
1489
1490
1491class ReturnsProperty(Property):
1492    arg_types = {"this": True, "is_table": False, "table": False}
1493
1494
1495class RowFormatDelimitedProperty(Property):
1496    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
1497    arg_types = {
1498        "fields": False,
1499        "escaped": False,
1500        "collection_items": False,
1501        "map_keys": False,
1502        "lines": False,
1503        "null": False,
1504        "serde": False,
1505    }
1506
1507
1508class RowFormatSerdeProperty(Property):
1509    arg_types = {"this": True}
1510
1511
1512class SchemaCommentProperty(Property):
1513    arg_types = {"this": True}
1514
1515
1516class SerdeProperties(Property):
1517    arg_types = {"expressions": True}
1518
1519
1520class SetProperty(Property):
1521    arg_types = {"multi": True}
1522
1523
1524class SortKeyProperty(Property):
1525    arg_types = {"this": True, "compound": False}
1526
1527
1528class SqlSecurityProperty(Property):
1529    arg_types = {"definer": True}
1530
1531
1532class TableFormatProperty(Property):
1533    arg_types = {"this": True}
1534
1535
1536class TemporaryProperty(Property):
1537    arg_types = {"global_": True}
1538
1539
1540class TransientProperty(Property):
1541    arg_types = {"this": False}
1542
1543
1544class VolatilityProperty(Property):
1545    arg_types = {"this": True}
1546
1547
1548class WithDataProperty(Property):
1549    arg_types = {"no": True, "statistics": False}
1550
1551
1552class WithJournalTableProperty(Property):
1553    arg_types = {"this": True}
1554
1555
1556class Properties(Expression):
1557    arg_types = {"expressions": True}
1558
1559    NAME_TO_PROPERTY = {
1560        "ALGORITHM": AlgorithmProperty,
1561        "AUTO_INCREMENT": AutoIncrementProperty,
1562        "CHARACTER SET": CharacterSetProperty,
1563        "COLLATE": CollateProperty,
1564        "COMMENT": SchemaCommentProperty,
1565        "DEFINER": DefinerProperty,
1566        "DISTKEY": DistKeyProperty,
1567        "DISTSTYLE": DistStyleProperty,
1568        "ENGINE": EngineProperty,
1569        "EXECUTE AS": ExecuteAsProperty,
1570        "FORMAT": FileFormatProperty,
1571        "LANGUAGE": LanguageProperty,
1572        "LOCATION": LocationProperty,
1573        "PARTITIONED_BY": PartitionedByProperty,
1574        "RETURNS": ReturnsProperty,
1575        "SORTKEY": SortKeyProperty,
1576        "TABLE_FORMAT": TableFormatProperty,
1577    }
1578
1579    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
1580
1581    # CREATE property locations
1582    # Form: schema specified
1583    #   create [POST_CREATE]
1584    #     table a [POST_NAME]
1585    #     (b int) [POST_SCHEMA]
1586    #     with ([POST_WITH])
1587    #     index (b) [POST_INDEX]
1588    #
1589    # Form: alias selection
1590    #   create [POST_CREATE]
1591    #     table a [POST_NAME]
1592    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
1593    #     index (c) [POST_INDEX]
1594    class Location(AutoName):
1595        POST_CREATE = auto()
1596        POST_NAME = auto()
1597        POST_SCHEMA = auto()
1598        POST_WITH = auto()
1599        POST_ALIAS = auto()
1600        POST_EXPRESSION = auto()
1601        POST_INDEX = auto()
1602        UNSUPPORTED = auto()
1603
1604    @classmethod
1605    def from_dict(cls, properties_dict) -> Properties:
1606        expressions = []
1607        for key, value in properties_dict.items():
1608            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
1609            if property_cls:
1610                expressions.append(property_cls(this=convert(value)))
1611            else:
1612                expressions.append(Property(this=Literal.string(key), value=convert(value)))
1613
1614        return cls(expressions=expressions)
1615
1616
1617class Qualify(Expression):
1618    pass
1619
1620
1621# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
1622class Return(Expression):
1623    pass
1624
1625
1626class Reference(Expression):
1627    arg_types = {"this": True, "expressions": False, "options": False}
1628
1629
1630class Tuple(Expression):
1631    arg_types = {"expressions": False}
1632
1633
1634class Subqueryable(Unionable):
1635    def subquery(self, alias=None, copy=True) -> Subquery:
1636        """
1637        Convert this expression to an aliased expression that can be used as a Subquery.
1638
1639        Example:
1640            >>> subquery = Select().select("x").from_("tbl").subquery()
1641            >>> Select().select("x").from_(subquery).sql()
1642            'SELECT x FROM (SELECT x FROM tbl)'
1643
1644        Args:
1645            alias (str | Identifier): an optional alias for the subquery
1646            copy (bool): if `False`, modify this expression instance in-place.
1647
1648        Returns:
1649            Alias: the subquery
1650        """
1651        instance = _maybe_copy(self, copy)
1652        return Subquery(
1653            this=instance,
1654            alias=TableAlias(this=to_identifier(alias)),
1655        )
1656
1657    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1658        raise NotImplementedError
1659
1660    @property
1661    def ctes(self):
1662        with_ = self.args.get("with")
1663        if not with_:
1664            return []
1665        return with_.expressions
1666
1667    @property
1668    def selects(self):
1669        raise NotImplementedError("Subqueryable objects must implement `selects`")
1670
1671    @property
1672    def named_selects(self):
1673        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
1674
1675    def with_(
1676        self,
1677        alias,
1678        as_,
1679        recursive=None,
1680        append=True,
1681        dialect=None,
1682        copy=True,
1683        **opts,
1684    ):
1685        """
1686        Append to or set the common table expressions.
1687
1688        Example:
1689            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1690            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1691
1692        Args:
1693            alias (str | Expression): the SQL code string to parse as the table name.
1694                If an `Expression` instance is passed, this is used as-is.
1695            as_ (str | Expression): the SQL code string to parse as the table expression.
1696                If an `Expression` instance is passed, it will be used as-is.
1697            recursive (bool): set the RECURSIVE part of the expression. Defaults to `False`.
1698            append (bool): if `True`, add to any existing expressions.
1699                Otherwise, this resets the expressions.
1700            dialect (str): the dialect used to parse the input expression.
1701            copy (bool): if `False`, modify this expression instance in-place.
1702            opts (kwargs): other options to use to parse the input expressions.
1703
1704        Returns:
1705            Select: the modified expression.
1706        """
1707        alias_expression = maybe_parse(
1708            alias,
1709            dialect=dialect,
1710            into=TableAlias,
1711            **opts,
1712        )
1713        as_expression = maybe_parse(
1714            as_,
1715            dialect=dialect,
1716            **opts,
1717        )
1718        cte = CTE(
1719            this=as_expression,
1720            alias=alias_expression,
1721        )
1722        return _apply_child_list_builder(
1723            cte,
1724            instance=self,
1725            arg="with",
1726            append=append,
1727            copy=copy,
1728            into=With,
1729            properties={"recursive": recursive or False},
1730        )
1731
1732
1733QUERY_MODIFIERS = {
1734    "match": False,
1735    "laterals": False,
1736    "joins": False,
1737    "pivots": False,
1738    "where": False,
1739    "group": False,
1740    "having": False,
1741    "qualify": False,
1742    "windows": False,
1743    "distribute": False,
1744    "sort": False,
1745    "cluster": False,
1746    "order": False,
1747    "limit": False,
1748    "offset": False,
1749    "lock": False,
1750}
1751
1752
1753class Table(Expression):
1754    arg_types = {
1755        "this": True,
1756        "alias": False,
1757        "db": False,
1758        "catalog": False,
1759        "laterals": False,
1760        "joins": False,
1761        "pivots": False,
1762        "hints": False,
1763        "system_time": False,
1764    }
1765
1766    @property
1767    def db(self) -> str:
1768        return self.text("db")
1769
1770    @property
1771    def catalog(self) -> str:
1772        return self.text("catalog")
1773
1774
1775# See the TSQL "Querying data in a system-versioned temporal table" page
1776class SystemTime(Expression):
1777    arg_types = {
1778        "this": False,
1779        "expression": False,
1780        "kind": True,
1781    }
1782
1783
1784class Union(Subqueryable):
1785    arg_types = {
1786        "with": False,
1787        "this": True,
1788        "expression": True,
1789        "distinct": False,
1790        **QUERY_MODIFIERS,
1791    }
1792
1793    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
1794        """
1795        Set the LIMIT expression.
1796
1797        Example:
1798            >>> select("1").union(select("1")).limit(1).sql()
1799            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
1800
1801        Args:
1802            expression (str | int | Expression): the SQL code string to parse.
1803                This can also be an integer.
1804                If a `Limit` instance is passed, this is used as-is.
1805                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1806            dialect (str): the dialect used to parse the input expression.
1807            copy (bool): if `False`, modify this expression instance in-place.
1808            opts (kwargs): other options to use to parse the input expressions.
1809
1810        Returns:
1811            Select: The limited subqueryable.
1812        """
1813        return (
1814            select("*")
1815            .from_(self.subquery(alias="_l_0", copy=copy))
1816            .limit(expression, dialect=dialect, copy=False, **opts)
1817        )
1818
1819    def select(
1820        self,
1821        *expressions: str | Expression,
1822        append: bool = True,
1823        dialect: DialectType = None,
1824        copy: bool = True,
1825        **opts,
1826    ) -> Union:
1827        """Append to or set the SELECT of the union recursively.
1828
1829        Example:
1830            >>> from sqlglot import parse_one
1831            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
1832            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
1833
1834        Args:
1835            *expressions: the SQL code strings to parse.
1836                If an `Expression` instance is passed, it will be used as-is.
1837            append: if `True`, add to any existing expressions.
1838                Otherwise, this resets the expressions.
1839            dialect: the dialect used to parse the input expressions.
1840            copy: if `False`, modify this expression instance in-place.
1841            opts: other options to use to parse the input expressions.
1842
1843        Returns:
1844            Union: the modified expression.
1845        """
1846        this = self.copy() if copy else self
1847        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
1848        this.expression.unnest().select(
1849            *expressions, append=append, dialect=dialect, copy=False, **opts
1850        )
1851        return this
1852
1853    @property
1854    def named_selects(self):
1855        return self.this.unnest().named_selects
1856
1857    @property
1858    def is_star(self) -> bool:
1859        return self.this.is_star or self.expression.is_star
1860
1861    @property
1862    def selects(self):
1863        return self.this.unnest().selects
1864
1865    @property
1866    def left(self):
1867        return self.this
1868
1869    @property
1870    def right(self):
1871        return self.expression
1872
1873
1874class Except(Union):
1875    pass
1876
1877
1878class Intersect(Union):
1879    pass
1880
1881
1882class Unnest(UDTF):
1883    arg_types = {
1884        "expressions": True,
1885        "ordinality": False,
1886        "alias": False,
1887        "offset": False,
1888    }
1889
1890
1891class Update(Expression):
1892    arg_types = {
1893        "with": False,
1894        "this": False,
1895        "expressions": True,
1896        "from": False,
1897        "where": False,
1898    }
1899
1900
1901class Values(UDTF):
1902    arg_types = {
1903        "expressions": True,
1904        "ordinality": False,
1905        "alias": False,
1906    }
1907
1908
1909class Var(Expression):
1910    pass
1911
1912
1913class Schema(Expression):
1914    arg_types = {"this": False, "expressions": False}
1915
1916
1917# Used to represent the FOR UPDATE and FOR SHARE locking read types.
1918# https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
1919class Lock(Expression):
1920    arg_types = {"update": True}
1921
1922
1923class Select(Subqueryable):
1924    arg_types = {
1925        "with": False,
1926        "expressions": False,
1927        "hint": False,
1928        "distinct": False,
1929        "into": False,
1930        "from": False,
1931        **QUERY_MODIFIERS,
1932    }
1933
1934    def from_(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
1935        """
1936        Set the FROM expression.
1937
1938        Example:
1939            >>> Select().from_("tbl").select("x").sql()
1940            'SELECT x FROM tbl'
1941
1942        Args:
1943            *expressions (str | Expression): the SQL code strings to parse.
1944                If a `From` instance is passed, this is used as-is.
1945                If another `Expression` instance is passed, it will be wrapped in a `From`.
1946            append (bool): if `True`, add to any existing expressions.
1947                Otherwise, this flattens all the `From` expression into a single expression.
1948            dialect (str): the dialect used to parse the input expression.
1949            copy (bool): if `False`, modify this expression instance in-place.
1950            opts (kwargs): other options to use to parse the input expressions.
1951
1952        Returns:
1953            Select: the modified expression.
1954        """
1955        return _apply_child_list_builder(
1956            *expressions,
1957            instance=self,
1958            arg="from",
1959            append=append,
1960            copy=copy,
1961            prefix="FROM",
1962            into=From,
1963            dialect=dialect,
1964            **opts,
1965        )
1966
1967    def group_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
1968        """
1969        Set the GROUP BY expression.
1970
1971        Example:
1972            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
1973            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
1974
1975        Args:
1976            *expressions (str | Expression): the SQL code strings to parse.
1977                If a `Group` instance is passed, this is used as-is.
1978                If another `Expression` instance is passed, it will be wrapped in a `Group`.
1979                If nothing is passed in then a group by is not applied to the expression
1980            append (bool): if `True`, add to any existing expressions.
1981                Otherwise, this flattens all the `Group` expression into a single expression.
1982            dialect (str): the dialect used to parse the input expression.
1983            copy (bool): if `False`, modify this expression instance in-place.
1984            opts (kwargs): other options to use to parse the input expressions.
1985
1986        Returns:
1987            Select: the modified expression.
1988        """
1989        if not expressions:
1990            return self if not copy else self.copy()
1991        return _apply_child_list_builder(
1992            *expressions,
1993            instance=self,
1994            arg="group",
1995            append=append,
1996            copy=copy,
1997            prefix="GROUP BY",
1998            into=Group,
1999            dialect=dialect,
2000            **opts,
2001        )
2002
2003    def order_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2004        """
2005        Set the ORDER BY expression.
2006
2007        Example:
2008            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2009            'SELECT x FROM tbl ORDER BY x DESC'
2010
2011        Args:
2012            *expressions (str | Expression): the SQL code strings to parse.
2013                If a `Group` instance is passed, this is used as-is.
2014                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2015            append (bool): if `True`, add to any existing expressions.
2016                Otherwise, this flattens all the `Order` expression into a single expression.
2017            dialect (str): the dialect used to parse the input expression.
2018            copy (bool): if `False`, modify this expression instance in-place.
2019            opts (kwargs): other options to use to parse the input expressions.
2020
2021        Returns:
2022            Select: the modified expression.
2023        """
2024        return _apply_child_list_builder(
2025            *expressions,
2026            instance=self,
2027            arg="order",
2028            append=append,
2029            copy=copy,
2030            prefix="ORDER BY",
2031            into=Order,
2032            dialect=dialect,
2033            **opts,
2034        )
2035
2036    def sort_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2037        """
2038        Set the SORT BY expression.
2039
2040        Example:
2041            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql()
2042            'SELECT x FROM tbl SORT BY x DESC'
2043
2044        Args:
2045            *expressions (str | Expression): the SQL code strings to parse.
2046                If a `Group` instance is passed, this is used as-is.
2047                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2048            append (bool): if `True`, add to any existing expressions.
2049                Otherwise, this flattens all the `Order` expression into a single expression.
2050            dialect (str): the dialect used to parse the input expression.
2051            copy (bool): if `False`, modify this expression instance in-place.
2052            opts (kwargs): other options to use to parse the input expressions.
2053
2054        Returns:
2055            Select: the modified expression.
2056        """
2057        return _apply_child_list_builder(
2058            *expressions,
2059            instance=self,
2060            arg="sort",
2061            append=append,
2062            copy=copy,
2063            prefix="SORT BY",
2064            into=Sort,
2065            dialect=dialect,
2066            **opts,
2067        )
2068
2069    def cluster_by(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2070        """
2071        Set the CLUSTER BY expression.
2072
2073        Example:
2074            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql()
2075            'SELECT x FROM tbl CLUSTER BY x DESC'
2076
2077        Args:
2078            *expressions (str | Expression): the SQL code strings to parse.
2079                If a `Group` instance is passed, this is used as-is.
2080                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2081            append (bool): if `True`, add to any existing expressions.
2082                Otherwise, this flattens all the `Order` expression into a single expression.
2083            dialect (str): the dialect used to parse the input expression.
2084            copy (bool): if `False`, modify this expression instance in-place.
2085            opts (kwargs): other options to use to parse the input expressions.
2086
2087        Returns:
2088            Select: the modified expression.
2089        """
2090        return _apply_child_list_builder(
2091            *expressions,
2092            instance=self,
2093            arg="cluster",
2094            append=append,
2095            copy=copy,
2096            prefix="CLUSTER BY",
2097            into=Cluster,
2098            dialect=dialect,
2099            **opts,
2100        )
2101
2102    def limit(self, expression, dialect=None, copy=True, **opts) -> Select:
2103        """
2104        Set the LIMIT expression.
2105
2106        Example:
2107            >>> Select().from_("tbl").select("x").limit(10).sql()
2108            'SELECT x FROM tbl LIMIT 10'
2109
2110        Args:
2111            expression (str | int | Expression): the SQL code string to parse.
2112                This can also be an integer.
2113                If a `Limit` instance is passed, this is used as-is.
2114                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2115            dialect (str): the dialect used to parse the input expression.
2116            copy (bool): if `False`, modify this expression instance in-place.
2117            opts (kwargs): other options to use to parse the input expressions.
2118
2119        Returns:
2120            Select: the modified expression.
2121        """
2122        return _apply_builder(
2123            expression=expression,
2124            instance=self,
2125            arg="limit",
2126            into=Limit,
2127            prefix="LIMIT",
2128            dialect=dialect,
2129            copy=copy,
2130            **opts,
2131        )
2132
2133    def offset(self, expression, dialect=None, copy=True, **opts) -> Select:
2134        """
2135        Set the OFFSET expression.
2136
2137        Example:
2138            >>> Select().from_("tbl").select("x").offset(10).sql()
2139            'SELECT x FROM tbl OFFSET 10'
2140
2141        Args:
2142            expression (str | int | Expression): the SQL code string to parse.
2143                This can also be an integer.
2144                If a `Offset` instance is passed, this is used as-is.
2145                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2146            dialect (str): the dialect used to parse the input expression.
2147            copy (bool): if `False`, modify this expression instance in-place.
2148            opts (kwargs): other options to use to parse the input expressions.
2149
2150        Returns:
2151            Select: the modified expression.
2152        """
2153        return _apply_builder(
2154            expression=expression,
2155            instance=self,
2156            arg="offset",
2157            into=Offset,
2158            prefix="OFFSET",
2159            dialect=dialect,
2160            copy=copy,
2161            **opts,
2162        )
2163
2164    def select(
2165        self,
2166        *expressions: str | Expression,
2167        append: bool = True,
2168        dialect: DialectType = None,
2169        copy: bool = True,
2170        **opts,
2171    ) -> Select:
2172        """
2173        Append to or set the SELECT expressions.
2174
2175        Example:
2176            >>> Select().select("x", "y").sql()
2177            'SELECT x, y'
2178
2179        Args:
2180            *expressions: the SQL code strings to parse.
2181                If an `Expression` instance is passed, it will be used as-is.
2182            append: if `True`, add to any existing expressions.
2183                Otherwise, this resets the expressions.
2184            dialect: the dialect used to parse the input expressions.
2185            copy: if `False`, modify this expression instance in-place.
2186            opts: other options to use to parse the input expressions.
2187
2188        Returns:
2189            Select: the modified expression.
2190        """
2191        return _apply_list_builder(
2192            *expressions,
2193            instance=self,
2194            arg="expressions",
2195            append=append,
2196            dialect=dialect,
2197            copy=copy,
2198            **opts,
2199        )
2200
2201    def lateral(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2202        """
2203        Append to or set the LATERAL expressions.
2204
2205        Example:
2206            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
2207            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
2208
2209        Args:
2210            *expressions (str | Expression): the SQL code strings to parse.
2211                If an `Expression` instance is passed, it will be used as-is.
2212            append (bool): if `True`, add to any existing expressions.
2213                Otherwise, this resets the expressions.
2214            dialect (str): the dialect used to parse the input expressions.
2215            copy (bool): if `False`, modify this expression instance in-place.
2216            opts (kwargs): other options to use to parse the input expressions.
2217
2218        Returns:
2219            Select: the modified expression.
2220        """
2221        return _apply_list_builder(
2222            *expressions,
2223            instance=self,
2224            arg="laterals",
2225            append=append,
2226            into=Lateral,
2227            prefix="LATERAL VIEW",
2228            dialect=dialect,
2229            copy=copy,
2230            **opts,
2231        )
2232
2233    def join(
2234        self,
2235        expression,
2236        on=None,
2237        using=None,
2238        append=True,
2239        join_type=None,
2240        join_alias=None,
2241        dialect=None,
2242        copy=True,
2243        **opts,
2244    ) -> Select:
2245        """
2246        Append to or set the JOIN expressions.
2247
2248        Example:
2249            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
2250            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
2251
2252            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
2253            'SELECT 1 FROM a JOIN b USING (x, y, z)'
2254
2255            Use `join_type` to change the type of join:
2256
2257            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
2258            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
2259
2260        Args:
2261            expression (str | Expression): the SQL code string to parse.
2262                If an `Expression` instance is passed, it will be used as-is.
2263            on (str | Expression): optionally specify the join "on" criteria as a SQL string.
2264                If an `Expression` instance is passed, it will be used as-is.
2265            using (str | Expression): optionally specify the join "using" criteria as a SQL string.
2266                If an `Expression` instance is passed, it will be used as-is.
2267            append (bool): if `True`, add to any existing expressions.
2268                Otherwise, this resets the expressions.
2269            join_type (str): If set, alter the parsed join type
2270            dialect (str): the dialect used to parse the input expressions.
2271            copy (bool): if `False`, modify this expression instance in-place.
2272            opts (kwargs): other options to use to parse the input expressions.
2273
2274        Returns:
2275            Select: the modified expression.
2276        """
2277        parse_args = {"dialect": dialect, **opts}
2278
2279        try:
2280            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
2281        except ParseError:
2282            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
2283
2284        join = expression if isinstance(expression, Join) else Join(this=expression)
2285
2286        if isinstance(join.this, Select):
2287            join.this.replace(join.this.subquery())
2288
2289        if join_type:
2290            natural: t.Optional[Token]
2291            side: t.Optional[Token]
2292            kind: t.Optional[Token]
2293
2294            natural, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
2295
2296            if natural:
2297                join.set("natural", True)
2298            if side:
2299                join.set("side", side.text)
2300            if kind:
2301                join.set("kind", kind.text)
2302
2303        if on:
2304            on = and_(*ensure_collection(on), dialect=dialect, **opts)
2305            join.set("on", on)
2306
2307        if using:
2308            join = _apply_list_builder(
2309                *ensure_collection(using),
2310                instance=join,
2311                arg="using",
2312                append=append,
2313                copy=copy,
2314                **opts,
2315            )
2316
2317        if join_alias:
2318            join.set("this", alias_(join.this, join_alias, table=True))
2319        return _apply_list_builder(
2320            join,
2321            instance=self,
2322            arg="joins",
2323            append=append,
2324            copy=copy,
2325            **opts,
2326        )
2327
2328    def where(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2329        """
2330        Append to or set the WHERE expressions.
2331
2332        Example:
2333            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
2334            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
2335
2336        Args:
2337            *expressions (str | Expression): the SQL code strings to parse.
2338                If an `Expression` instance is passed, it will be used as-is.
2339                Multiple expressions are combined with an AND operator.
2340            append (bool): if `True`, AND the new expressions to any existing expression.
2341                Otherwise, this resets the expression.
2342            dialect (str): the dialect used to parse the input expressions.
2343            copy (bool): if `False`, modify this expression instance in-place.
2344            opts (kwargs): other options to use to parse the input expressions.
2345
2346        Returns:
2347            Select: the modified expression.
2348        """
2349        return _apply_conjunction_builder(
2350            *expressions,
2351            instance=self,
2352            arg="where",
2353            append=append,
2354            into=Where,
2355            dialect=dialect,
2356            copy=copy,
2357            **opts,
2358        )
2359
2360    def having(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2361        """
2362        Append to or set the HAVING expressions.
2363
2364        Example:
2365            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
2366            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
2367
2368        Args:
2369            *expressions (str | Expression): the SQL code strings to parse.
2370                If an `Expression` instance is passed, it will be used as-is.
2371                Multiple expressions are combined with an AND operator.
2372            append (bool): if `True`, AND the new expressions to any existing expression.
2373                Otherwise, this resets the expression.
2374            dialect (str): the dialect used to parse the input expressions.
2375            copy (bool): if `False`, modify this expression instance in-place.
2376            opts (kwargs): other options to use to parse the input expressions.
2377
2378        Returns:
2379            Select: the modified expression.
2380        """
2381        return _apply_conjunction_builder(
2382            *expressions,
2383            instance=self,
2384            arg="having",
2385            append=append,
2386            into=Having,
2387            dialect=dialect,
2388            copy=copy,
2389            **opts,
2390        )
2391
2392    def window(self, *expressions, append=True, dialect=None, copy=True, **opts) -> Select:
2393        return _apply_list_builder(
2394            *expressions,
2395            instance=self,
2396            arg="windows",
2397            append=append,
2398            into=Window,
2399            dialect=dialect,
2400            copy=copy,
2401            **opts,
2402        )
2403
2404    def distinct(self, distinct=True, copy=True) -> Select:
2405        """
2406        Set the OFFSET expression.
2407
2408        Example:
2409            >>> Select().from_("tbl").select("x").distinct().sql()
2410            'SELECT DISTINCT x FROM tbl'
2411
2412        Args:
2413            distinct (bool): whether the Select should be distinct
2414            copy (bool): if `False`, modify this expression instance in-place.
2415
2416        Returns:
2417            Select: the modified expression.
2418        """
2419        instance = _maybe_copy(self, copy)
2420        instance.set("distinct", Distinct() if distinct else None)
2421        return instance
2422
2423    def ctas(self, table, properties=None, dialect=None, copy=True, **opts) -> Create:
2424        """
2425        Convert this expression to a CREATE TABLE AS statement.
2426
2427        Example:
2428            >>> Select().select("*").from_("tbl").ctas("x").sql()
2429            'CREATE TABLE x AS SELECT * FROM tbl'
2430
2431        Args:
2432            table (str | Expression): the SQL code string to parse as the table name.
2433                If another `Expression` instance is passed, it will be used as-is.
2434            properties (dict): an optional mapping of table properties
2435            dialect (str): the dialect used to parse the input table.
2436            copy (bool): if `False`, modify this expression instance in-place.
2437            opts (kwargs): other options to use to parse the input table.
2438
2439        Returns:
2440            Create: the CREATE TABLE AS expression
2441        """
2442        instance = _maybe_copy(self, copy)
2443        table_expression = maybe_parse(
2444            table,
2445            into=Table,
2446            dialect=dialect,
2447            **opts,
2448        )
2449        properties_expression = None
2450        if properties:
2451            properties_expression = Properties.from_dict(properties)
2452
2453        return Create(
2454            this=table_expression,
2455            kind="table",
2456            expression=instance,
2457            properties=properties_expression,
2458        )
2459
2460    def lock(self, update: bool = True, copy: bool = True) -> Select:
2461        """
2462        Set the locking read mode for this expression.
2463
2464        Examples:
2465            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
2466            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
2467
2468            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
2469            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
2470
2471        Args:
2472            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
2473            copy: if `False`, modify this expression instance in-place.
2474
2475        Returns:
2476            The modified expression.
2477        """
2478
2479        inst = _maybe_copy(self, copy)
2480        inst.set("lock", Lock(update=update))
2481
2482        return inst
2483
2484    @property
2485    def named_selects(self) -> t.List[str]:
2486        return [e.output_name for e in self.expressions if e.alias_or_name]
2487
2488    @property
2489    def is_star(self) -> bool:
2490        return any(expression.is_star for expression in self.expressions)
2491
2492    @property
2493    def selects(self) -> t.List[Expression]:
2494        return self.expressions
2495
2496
2497class Subquery(DerivedTable, Unionable):
2498    arg_types = {
2499        "this": True,
2500        "alias": False,
2501        "with": False,
2502        **QUERY_MODIFIERS,
2503    }
2504
2505    def unnest(self):
2506        """
2507        Returns the first non subquery.
2508        """
2509        expression = self
2510        while isinstance(expression, Subquery):
2511            expression = expression.this
2512        return expression
2513
2514    @property
2515    def is_star(self) -> bool:
2516        return self.this.is_star
2517
2518    @property
2519    def output_name(self):
2520        return self.alias
2521
2522
2523class TableSample(Expression):
2524    arg_types = {
2525        "this": False,
2526        "method": False,
2527        "bucket_numerator": False,
2528        "bucket_denominator": False,
2529        "bucket_field": False,
2530        "percent": False,
2531        "rows": False,
2532        "size": False,
2533        "seed": False,
2534    }
2535
2536
2537class Tag(Expression):
2538    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
2539
2540    arg_types = {
2541        "this": False,
2542        "prefix": False,
2543        "postfix": False,
2544    }
2545
2546
2547class Pivot(Expression):
2548    arg_types = {
2549        "this": False,
2550        "alias": False,
2551        "expressions": True,
2552        "field": True,
2553        "unpivot": True,
2554    }
2555
2556
2557class Window(Expression):
2558    arg_types = {
2559        "this": True,
2560        "partition_by": False,
2561        "order": False,
2562        "spec": False,
2563        "alias": False,
2564    }
2565
2566
2567class WindowSpec(Expression):
2568    arg_types = {
2569        "kind": False,
2570        "start": False,
2571        "start_side": False,
2572        "end": False,
2573        "end_side": False,
2574    }
2575
2576
2577class Where(Expression):
2578    pass
2579
2580
2581class Star(Expression):
2582    arg_types = {"except": False, "replace": False}
2583
2584    @property
2585    def name(self) -> str:
2586        return "*"
2587
2588    @property
2589    def output_name(self):
2590        return self.name
2591
2592
2593class Parameter(Expression):
2594    arg_types = {"this": True, "wrapped": False}
2595
2596
2597class SessionParameter(Expression):
2598    arg_types = {"this": True, "kind": False}
2599
2600
2601class Placeholder(Expression):
2602    arg_types = {"this": False}
2603
2604
2605class Null(Condition):
2606    arg_types: t.Dict[str, t.Any] = {}
2607
2608    @property
2609    def name(self) -> str:
2610        return "NULL"
2611
2612
2613class Boolean(Condition):
2614    pass
2615
2616
2617class DataType(Expression):
2618    arg_types = {
2619        "this": True,
2620        "expressions": False,
2621        "nested": False,
2622        "values": False,
2623        "prefix": False,
2624    }
2625
2626    class Type(AutoName):
2627        CHAR = auto()
2628        NCHAR = auto()
2629        VARCHAR = auto()
2630        NVARCHAR = auto()
2631        TEXT = auto()
2632        MEDIUMTEXT = auto()
2633        LONGTEXT = auto()
2634        MEDIUMBLOB = auto()
2635        LONGBLOB = auto()
2636        BINARY = auto()
2637        VARBINARY = auto()
2638        INT = auto()
2639        TINYINT = auto()
2640        SMALLINT = auto()
2641        BIGINT = auto()
2642        FLOAT = auto()
2643        DOUBLE = auto()
2644        DECIMAL = auto()
2645        BOOLEAN = auto()
2646        JSON = auto()
2647        JSONB = auto()
2648        INTERVAL = auto()
2649        TIME = auto()
2650        TIMESTAMP = auto()
2651        TIMESTAMPTZ = auto()
2652        TIMESTAMPLTZ = auto()
2653        DATE = auto()
2654        DATETIME = auto()
2655        ARRAY = auto()
2656        MAP = auto()
2657        UUID = auto()
2658        GEOGRAPHY = auto()
2659        GEOMETRY = auto()
2660        STRUCT = auto()
2661        NULLABLE = auto()
2662        HLLSKETCH = auto()
2663        HSTORE = auto()
2664        SUPER = auto()
2665        SERIAL = auto()
2666        SMALLSERIAL = auto()
2667        BIGSERIAL = auto()
2668        XML = auto()
2669        UNIQUEIDENTIFIER = auto()
2670        MONEY = auto()
2671        SMALLMONEY = auto()
2672        ROWVERSION = auto()
2673        IMAGE = auto()
2674        VARIANT = auto()
2675        OBJECT = auto()
2676        INET = auto()
2677        NULL = auto()
2678        UNKNOWN = auto()  # Sentinel value, useful for type annotation
2679
2680    TEXT_TYPES = {
2681        Type.CHAR,
2682        Type.NCHAR,
2683        Type.VARCHAR,
2684        Type.NVARCHAR,
2685        Type.TEXT,
2686    }
2687
2688    INTEGER_TYPES = {
2689        Type.INT,
2690        Type.TINYINT,
2691        Type.SMALLINT,
2692        Type.BIGINT,
2693    }
2694
2695    FLOAT_TYPES = {
2696        Type.FLOAT,
2697        Type.DOUBLE,
2698    }
2699
2700    NUMERIC_TYPES = {*INTEGER_TYPES, *FLOAT_TYPES}
2701
2702    TEMPORAL_TYPES = {
2703        Type.TIMESTAMP,
2704        Type.TIMESTAMPTZ,
2705        Type.TIMESTAMPLTZ,
2706        Type.DATE,
2707        Type.DATETIME,
2708    }
2709
2710    @classmethod
2711    def build(
2712        cls, dtype: str | DataType | DataType.Type, dialect: DialectType = None, **kwargs
2713    ) -> DataType:
2714        from sqlglot import parse_one
2715
2716        if isinstance(dtype, str):
2717            if dtype.upper() in cls.Type.__members__:
2718                data_type_exp: t.Optional[Expression] = DataType(this=DataType.Type[dtype.upper()])
2719            else:
2720                data_type_exp = parse_one(dtype, read=dialect, into=DataType)
2721            if data_type_exp is None:
2722                raise ValueError(f"Unparsable data type value: {dtype}")
2723        elif isinstance(dtype, DataType.Type):
2724            data_type_exp = DataType(this=dtype)
2725        elif isinstance(dtype, DataType):
2726            return dtype
2727        else:
2728            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
2729        return DataType(**{**data_type_exp.args, **kwargs})
2730
2731    def is_type(self, dtype: DataType.Type) -> bool:
2732        return self.this == dtype
2733
2734
2735# https://www.postgresql.org/docs/15/datatype-pseudo.html
2736class PseudoType(Expression):
2737    pass
2738
2739
2740class StructKwarg(Expression):
2741    arg_types = {"this": True, "expression": True}
2742
2743
2744# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
2745class SubqueryPredicate(Predicate):
2746    pass
2747
2748
2749class All(SubqueryPredicate):
2750    pass
2751
2752
2753class Any(SubqueryPredicate):
2754    pass
2755
2756
2757class Exists(SubqueryPredicate):
2758    pass
2759
2760
2761# Commands to interact with the databases or engines. For most of the command
2762# expressions we parse whatever comes after the command's name as a string.
2763class Command(Expression):
2764    arg_types = {"this": True, "expression": False}
2765
2766
2767class Transaction(Expression):
2768    arg_types = {"this": False, "modes": False}
2769
2770
2771class Commit(Expression):
2772    arg_types = {"chain": False}
2773
2774
2775class Rollback(Expression):
2776    arg_types = {"savepoint": False}
2777
2778
2779class AlterTable(Expression):
2780    arg_types = {"this": True, "actions": True, "exists": False}
2781
2782
2783class AddConstraint(Expression):
2784    arg_types = {"this": False, "expression": False, "enforced": False}
2785
2786
2787class DropPartition(Expression):
2788    arg_types = {"expressions": True, "exists": False}
2789
2790
2791# Binary expressions like (ADD a b)
2792class Binary(Expression):
2793    arg_types = {"this": True, "expression": True}
2794
2795    @property
2796    def left(self):
2797        return self.this
2798
2799    @property
2800    def right(self):
2801        return self.expression
2802
2803
2804class Add(Binary):
2805    pass
2806
2807
2808class Connector(Binary, Condition):
2809    pass
2810
2811
2812class And(Connector):
2813    pass
2814
2815
2816class Or(Connector):
2817    pass
2818
2819
2820class BitwiseAnd(Binary):
2821    pass
2822
2823
2824class BitwiseLeftShift(Binary):
2825    pass
2826
2827
2828class BitwiseOr(Binary):
2829    pass
2830
2831
2832class BitwiseRightShift(Binary):
2833    pass
2834
2835
2836class BitwiseXor(Binary):
2837    pass
2838
2839
2840class Div(Binary):
2841    pass
2842
2843
2844class FloatDiv(Binary):
2845    pass
2846
2847
2848class Overlaps(Binary):
2849    pass
2850
2851
2852class Dot(Binary):
2853    @property
2854    def name(self) -> str:
2855        return self.expression.name
2856
2857
2858class DPipe(Binary):
2859    pass
2860
2861
2862class EQ(Binary, Predicate):
2863    pass
2864
2865
2866class NullSafeEQ(Binary, Predicate):
2867    pass
2868
2869
2870class NullSafeNEQ(Binary, Predicate):
2871    pass
2872
2873
2874class Distance(Binary):
2875    pass
2876
2877
2878class Escape(Binary):
2879    pass
2880
2881
2882class Glob(Binary, Predicate):
2883    pass
2884
2885
2886class GT(Binary, Predicate):
2887    pass
2888
2889
2890class GTE(Binary, Predicate):
2891    pass
2892
2893
2894class ILike(Binary, Predicate):
2895    pass
2896
2897
2898class ILikeAny(Binary, Predicate):
2899    pass
2900
2901
2902class IntDiv(Binary):
2903    pass
2904
2905
2906class Is(Binary, Predicate):
2907    pass
2908
2909
2910class Kwarg(Binary):
2911    """Kwarg in special functions like func(kwarg => y)."""
2912
2913
2914class Like(Binary, Predicate):
2915    pass
2916
2917
2918class LikeAny(Binary, Predicate):
2919    pass
2920
2921
2922class LT(Binary, Predicate):
2923    pass
2924
2925
2926class LTE(Binary, Predicate):
2927    pass
2928
2929
2930class Mod(Binary):
2931    pass
2932
2933
2934class Mul(Binary):
2935    pass
2936
2937
2938class NEQ(Binary, Predicate):
2939    pass
2940
2941
2942class SimilarTo(Binary, Predicate):
2943    pass
2944
2945
2946class Slice(Binary):
2947    arg_types = {"this": False, "expression": False}
2948
2949
2950class Sub(Binary):
2951    pass
2952
2953
2954# Unary Expressions
2955# (NOT a)
2956class Unary(Expression):
2957    pass
2958
2959
2960class BitwiseNot(Unary):
2961    pass
2962
2963
2964class Not(Unary, Condition):
2965    pass
2966
2967
2968class Paren(Unary, Condition):
2969    arg_types = {"this": True, "with": False}
2970
2971
2972class Neg(Unary):
2973    pass
2974
2975
2976# Special Functions
2977class Alias(Expression):
2978    arg_types = {"this": True, "alias": False}
2979
2980    @property
2981    def output_name(self):
2982        return self.alias
2983
2984
2985class Aliases(Expression):
2986    arg_types = {"this": True, "expressions": True}
2987
2988    @property
2989    def aliases(self):
2990        return self.expressions
2991
2992
2993class AtTimeZone(Expression):
2994    arg_types = {"this": True, "zone": True}
2995
2996
2997class Between(Predicate):
2998    arg_types = {"this": True, "low": True, "high": True}
2999
3000
3001class Bracket(Condition):
3002    arg_types = {"this": True, "expressions": True}
3003
3004
3005class Distinct(Expression):
3006    arg_types = {"expressions": False, "on": False}
3007
3008
3009class In(Predicate):
3010    arg_types = {
3011        "this": True,
3012        "expressions": False,
3013        "query": False,
3014        "unnest": False,
3015        "field": False,
3016        "is_global": False,
3017    }
3018
3019
3020class TimeUnit(Expression):
3021    """Automatically converts unit arg into a var."""
3022
3023    arg_types = {"unit": False}
3024
3025    def __init__(self, **args):
3026        unit = args.get("unit")
3027        if isinstance(unit, Column):
3028            args["unit"] = Var(this=unit.name)
3029        elif isinstance(unit, Week):
3030            unit.set("this", Var(this=unit.this.name))
3031        super().__init__(**args)
3032
3033
3034class Interval(TimeUnit):
3035    arg_types = {"this": False, "unit": False}
3036
3037
3038class IgnoreNulls(Expression):
3039    pass
3040
3041
3042class RespectNulls(Expression):
3043    pass
3044
3045
3046# Functions
3047class Func(Condition):
3048    """
3049    The base class for all function expressions.
3050
3051    Attributes:
3052        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
3053            treated as a variable length argument and the argument's value will be stored as a list.
3054        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
3055            for this function expression. These values are used to map this node to a name during parsing
3056            as well as to provide the function's name during SQL string generation. By default the SQL
3057            name is set to the expression's class name transformed to snake case.
3058    """
3059
3060    is_var_len_args = False
3061
3062    @classmethod
3063    def from_arg_list(cls, args):
3064        if cls.is_var_len_args:
3065            all_arg_keys = list(cls.arg_types)
3066            # If this function supports variable length argument treat the last argument as such.
3067            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
3068            num_non_var = len(non_var_len_arg_keys)
3069
3070            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
3071            args_dict[all_arg_keys[-1]] = args[num_non_var:]
3072        else:
3073            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
3074
3075        return cls(**args_dict)
3076
3077    @classmethod
3078    def sql_names(cls):
3079        if cls is Func:
3080            raise NotImplementedError(
3081                "SQL name is only supported by concrete function implementations"
3082            )
3083        if "_sql_names" not in cls.__dict__:
3084            cls._sql_names = [camel_to_snake_case(cls.__name__)]
3085        return cls._sql_names
3086
3087    @classmethod
3088    def sql_name(cls):
3089        return cls.sql_names()[0]
3090
3091    @classmethod
3092    def default_parser_mappings(cls):
3093        return {name: cls.from_arg_list for name in cls.sql_names()}
3094
3095
3096class AggFunc(Func):
3097    pass
3098
3099
3100class Abs(Func):
3101    pass
3102
3103
3104class Anonymous(Func):
3105    arg_types = {"this": True, "expressions": False}
3106    is_var_len_args = True
3107
3108
3109class ApproxDistinct(AggFunc):
3110    arg_types = {"this": True, "accuracy": False}
3111
3112
3113class Array(Func):
3114    arg_types = {"expressions": False}
3115    is_var_len_args = True
3116
3117
3118class GenerateSeries(Func):
3119    arg_types = {"start": True, "end": True, "step": False}
3120
3121
3122class ArrayAgg(AggFunc):
3123    pass
3124
3125
3126class ArrayAll(Func):
3127    arg_types = {"this": True, "expression": True}
3128
3129
3130class ArrayAny(Func):
3131    arg_types = {"this": True, "expression": True}
3132
3133
3134class ArrayConcat(Func):
3135    arg_types = {"this": True, "expressions": False}
3136    is_var_len_args = True
3137
3138
3139class ArrayContains(Func):
3140    arg_types = {"this": True, "expression": True}
3141
3142
3143class ArrayFilter(Func):
3144    arg_types = {"this": True, "expression": True}
3145    _sql_names = ["FILTER", "ARRAY_FILTER"]
3146
3147
3148class ArrayJoin(Func):
3149    arg_types = {"this": True, "expression": True, "null": False}
3150
3151
3152class ArraySize(Func):
3153    arg_types = {"this": True, "expression": False}
3154
3155
3156class ArraySort(Func):
3157    arg_types = {"this": True, "expression": False}
3158
3159
3160class ArraySum(Func):
3161    pass
3162
3163
3164class ArrayUnionAgg(AggFunc):
3165    pass
3166
3167
3168class Avg(AggFunc):
3169    pass
3170
3171
3172class AnyValue(AggFunc):
3173    pass
3174
3175
3176class Case(Func):
3177    arg_types = {"this": False, "ifs": True, "default": False}
3178
3179
3180class Cast(Func):
3181    arg_types = {"this": True, "to": True}
3182
3183    @property
3184    def name(self) -> str:
3185        return self.this.name
3186
3187    @property
3188    def to(self):
3189        return self.args["to"]
3190
3191    @property
3192    def output_name(self):
3193        return self.name
3194
3195    def is_type(self, dtype: DataType.Type) -> bool:
3196        return self.to.is_type(dtype)
3197
3198
3199class Collate(Binary):
3200    pass
3201
3202
3203class TryCast(Cast):
3204    pass
3205
3206
3207class Ceil(Func):
3208    arg_types = {"this": True, "decimals": False}
3209    _sql_names = ["CEIL", "CEILING"]
3210
3211
3212class Coalesce(Func):
3213    arg_types = {"this": True, "expressions": False}
3214    is_var_len_args = True
3215
3216
3217class Concat(Func):
3218    arg_types = {"expressions": True}
3219    is_var_len_args = True
3220
3221
3222class ConcatWs(Concat):
3223    _sql_names = ["CONCAT_WS"]
3224
3225
3226class Count(AggFunc):
3227    arg_types = {"this": False}
3228
3229
3230class CurrentDate(Func):
3231    arg_types = {"this": False}
3232
3233
3234class CurrentDatetime(Func):
3235    arg_types = {"this": False}
3236
3237
3238class CurrentTime(Func):
3239    arg_types = {"this": False}
3240
3241
3242class CurrentTimestamp(Func):
3243    arg_types = {"this": False}
3244
3245
3246class DateAdd(Func, TimeUnit):
3247    arg_types = {"this": True, "expression": True, "unit": False}
3248
3249
3250class DateSub(Func, TimeUnit):
3251    arg_types = {"this": True, "expression": True, "unit": False}
3252
3253
3254class DateDiff(Func, TimeUnit):
3255    arg_types = {"this": True, "expression": True, "unit": False}
3256
3257
3258class DateTrunc(Func):
3259    arg_types = {"unit": True, "this": True, "zone": False}
3260
3261
3262class DatetimeAdd(Func, TimeUnit):
3263    arg_types = {"this": True, "expression": True, "unit": False}
3264
3265
3266class DatetimeSub(Func, TimeUnit):
3267    arg_types = {"this": True, "expression": True, "unit": False}
3268
3269
3270class DatetimeDiff(Func, TimeUnit):
3271    arg_types = {"this": True, "expression": True, "unit": False}
3272
3273
3274class DatetimeTrunc(Func, TimeUnit):
3275    arg_types = {"this": True, "unit": True, "zone": False}
3276
3277
3278class DayOfWeek(Func):
3279    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
3280
3281
3282class DayOfMonth(Func):
3283    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
3284
3285
3286class DayOfYear(Func):
3287    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
3288
3289
3290class WeekOfYear(Func):
3291    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
3292
3293
3294class LastDateOfMonth(Func):
3295    pass
3296
3297
3298class Extract(Func):
3299    arg_types = {"this": True, "expression": True}
3300
3301
3302class TimestampAdd(Func, TimeUnit):
3303    arg_types = {"this": True, "expression": True, "unit": False}
3304
3305
3306class TimestampSub(Func, TimeUnit):
3307    arg_types = {"this": True, "expression": True, "unit": False}
3308
3309
3310class TimestampDiff(Func, TimeUnit):
3311    arg_types = {"this": True, "expression": True, "unit": False}
3312
3313
3314class TimestampTrunc(Func, TimeUnit):
3315    arg_types = {"this": True, "unit": True, "zone": False}
3316
3317
3318class TimeAdd(Func, TimeUnit):
3319    arg_types = {"this": True, "expression": True, "unit": False}
3320
3321
3322class TimeSub(Func, TimeUnit):
3323    arg_types = {"this": True, "expression": True, "unit": False}
3324
3325
3326class TimeDiff(Func, TimeUnit):
3327    arg_types = {"this": True, "expression": True, "unit": False}
3328
3329
3330class TimeTrunc(Func, TimeUnit):
3331    arg_types = {"this": True, "unit": True, "zone": False}
3332
3333
3334class DateFromParts(Func):
3335    _sql_names = ["DATEFROMPARTS"]
3336    arg_types = {"year": True, "month": True, "day": True}
3337
3338
3339class DateStrToDate(Func):
3340    pass
3341
3342
3343class DateToDateStr(Func):
3344    pass
3345
3346
3347class DateToDi(Func):
3348    pass
3349
3350
3351class Day(Func):
3352    pass
3353
3354
3355class Decode(Func):
3356    arg_types = {"this": True, "charset": True, "replace": False}
3357
3358
3359class DiToDate(Func):
3360    pass
3361
3362
3363class Encode(Func):
3364    arg_types = {"this": True, "charset": True}
3365
3366
3367class Exp(Func):
3368    pass
3369
3370
3371class Explode(Func):
3372    pass
3373
3374
3375class Floor(Func):
3376    arg_types = {"this": True, "decimals": False}
3377
3378
3379class Greatest(Func):
3380    arg_types = {"this": True, "expressions": False}
3381    is_var_len_args = True
3382
3383
3384class GroupConcat(Func):
3385    arg_types = {"this": True, "separator": False}
3386
3387
3388class Hex(Func):
3389    pass
3390
3391
3392class If(Func):
3393    arg_types = {"this": True, "true": True, "false": False}
3394
3395
3396class IfNull(Func):
3397    arg_types = {"this": True, "expression": False}
3398    _sql_names = ["IFNULL", "NVL"]
3399
3400
3401class Initcap(Func):
3402    pass
3403
3404
3405class JSONBContains(Binary):
3406    _sql_names = ["JSONB_CONTAINS"]
3407
3408
3409class JSONExtract(Binary, Func):
3410    _sql_names = ["JSON_EXTRACT"]
3411
3412
3413class JSONExtractScalar(JSONExtract):
3414    _sql_names = ["JSON_EXTRACT_SCALAR"]
3415
3416
3417class JSONBExtract(JSONExtract):
3418    _sql_names = ["JSONB_EXTRACT"]
3419
3420
3421class JSONBExtractScalar(JSONExtract):
3422    _sql_names = ["JSONB_EXTRACT_SCALAR"]
3423
3424
3425class Least(Func):
3426    arg_types = {"this": True, "expressions": False}
3427    is_var_len_args = True
3428
3429
3430class Length(Func):
3431    pass
3432
3433
3434class Levenshtein(Func):
3435    arg_types = {
3436        "this": True,
3437        "expression": False,
3438        "ins_cost": False,
3439        "del_cost": False,
3440        "sub_cost": False,
3441    }
3442
3443
3444class Ln(Func):
3445    pass
3446
3447
3448class Log(Func):
3449    arg_types = {"this": True, "expression": False}
3450
3451
3452class Log2(Func):
3453    pass
3454
3455
3456class Log10(Func):
3457    pass
3458
3459
3460class LogicalOr(AggFunc):
3461    _sql_names = ["LOGICAL_OR", "BOOL_OR"]
3462
3463
3464class Lower(Func):
3465    _sql_names = ["LOWER", "LCASE"]
3466
3467
3468class Map(Func):
3469    arg_types = {"keys": False, "values": False}
3470
3471
3472class VarMap(Func):
3473    arg_types = {"keys": True, "values": True}
3474    is_var_len_args = True
3475
3476
3477class Matches(Func):
3478    """Oracle/Snowflake decode.
3479    https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm
3480    Pattern matching MATCHES(value, search1, result1, ...searchN, resultN, else)
3481    """
3482
3483    arg_types = {"this": True, "expressions": True}
3484    is_var_len_args = True
3485
3486
3487class Max(AggFunc):
3488    arg_types = {"this": True, "expression": False}
3489
3490
3491class Min(AggFunc):
3492    arg_types = {"this": True, "expression": False}
3493
3494
3495class Month(Func):
3496    pass
3497
3498
3499class Nvl2(Func):
3500    arg_types = {"this": True, "true": True, "false": False}
3501
3502
3503class Posexplode(Func):
3504    pass
3505
3506
3507class Pow(Binary, Func):
3508    _sql_names = ["POWER", "POW"]
3509
3510
3511class PercentileCont(AggFunc):
3512    pass
3513
3514
3515class PercentileDisc(AggFunc):
3516    pass
3517
3518
3519class Quantile(AggFunc):
3520    arg_types = {"this": True, "quantile": True}
3521
3522
3523# Clickhouse-specific:
3524# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/quantiles/#quantiles
3525class Quantiles(AggFunc):
3526    arg_types = {"parameters": True, "expressions": True}
3527
3528
3529class QuantileIf(AggFunc):
3530    arg_types = {"parameters": True, "expressions": True}
3531
3532
3533class ApproxQuantile(Quantile):
3534    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
3535
3536
3537class RangeN(Func):
3538    arg_types = {"this": True, "expressions": True, "each": False}
3539
3540
3541class ReadCSV(Func):
3542    _sql_names = ["READ_CSV"]
3543    is_var_len_args = True
3544    arg_types = {"this": True, "expressions": False}
3545
3546
3547class Reduce(Func):
3548    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
3549
3550
3551class RegexpExtract(Func):
3552    arg_types = {
3553        "this": True,
3554        "expression": True,
3555        "position": False,
3556        "occurrence": False,
3557        "group": False,
3558    }
3559
3560
3561class RegexpLike(Func):
3562    arg_types = {"this": True, "expression": True, "flag": False}
3563
3564
3565class RegexpILike(Func):
3566    arg_types = {"this": True, "expression": True, "flag": False}
3567
3568
3569class RegexpSplit(Func):
3570    arg_types = {"this": True, "expression": True}
3571
3572
3573class Repeat(Func):
3574    arg_types = {"this": True, "times": True}
3575
3576
3577class Round(Func):
3578    arg_types = {"this": True, "decimals": False}
3579
3580
3581class RowNumber(Func):
3582    arg_types: t.Dict[str, t.Any] = {}
3583
3584
3585class SafeDivide(Func):
3586    arg_types = {"this": True, "expression": True}
3587
3588
3589class SetAgg(AggFunc):
3590    pass
3591
3592
3593class SortArray(Func):
3594    arg_types = {"this": True, "asc": False}
3595
3596
3597class Split(Func):
3598    arg_types = {"this": True, "expression": True, "limit": False}
3599
3600
3601# Start may be omitted in the case of postgres
3602# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
3603class Substring(Func):
3604    arg_types = {"this": True, "start": False, "length": False}
3605
3606
3607class StrPosition(Func):
3608    arg_types = {
3609        "this": True,
3610        "substr": True,
3611        "position": False,
3612        "instance": False,
3613    }
3614
3615
3616class StrToDate(Func):
3617    arg_types = {"this": True, "format": True}
3618
3619
3620class StrToTime(Func):
3621    arg_types = {"this": True, "format": True}
3622
3623
3624# Spark allows unix_timestamp()
3625# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
3626class StrToUnix(Func):
3627    arg_types = {"this": False, "format": False}
3628
3629
3630class NumberToStr(Func):
3631    arg_types = {"this": True, "format": True}
3632
3633
3634class Struct(Func):
3635    arg_types = {"expressions": True}
3636    is_var_len_args = True
3637
3638
3639class StructExtract(Func):
3640    arg_types = {"this": True, "expression": True}
3641
3642
3643class Sum(AggFunc):
3644    pass
3645
3646
3647class Sqrt(Func):
3648    pass
3649
3650
3651class Stddev(AggFunc):
3652    pass
3653
3654
3655class StddevPop(AggFunc):
3656    pass
3657
3658
3659class StddevSamp(AggFunc):
3660    pass
3661
3662
3663class TimeToStr(Func):
3664    arg_types = {"this": True, "format": True}
3665
3666
3667class TimeToTimeStr(Func):
3668    pass
3669
3670
3671class TimeToUnix(Func):
3672    pass
3673
3674
3675class TimeStrToDate(Func):
3676    pass
3677
3678
3679class TimeStrToTime(Func):
3680    pass
3681
3682
3683class TimeStrToUnix(Func):
3684    pass
3685
3686
3687class Trim(Func):
3688    arg_types = {
3689        "this": True,
3690        "expression": False,
3691        "position": False,
3692        "collation": False,
3693    }
3694
3695
3696class TsOrDsAdd(Func, TimeUnit):
3697    arg_types = {"this": True, "expression": True, "unit": False}
3698
3699
3700class TsOrDsToDateStr(Func):
3701    pass
3702
3703
3704class TsOrDsToDate(Func):
3705    arg_types = {"this": True, "format": False}
3706
3707
3708class TsOrDiToDi(Func):
3709    pass
3710
3711
3712class Unhex(Func):
3713    pass
3714
3715
3716class UnixToStr(Func):
3717    arg_types = {"this": True, "format": False}
3718
3719
3720# https://prestodb.io/docs/current/functions/datetime.html
3721# presto has weird zone/hours/minutes
3722class UnixToTime(Func):
3723    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
3724
3725    SECONDS = Literal.string("seconds")
3726    MILLIS = Literal.string("millis")
3727    MICROS = Literal.string("micros")
3728
3729
3730class UnixToTimeStr(Func):
3731    pass
3732
3733
3734class Upper(Func):
3735    _sql_names = ["UPPER", "UCASE"]
3736
3737
3738class Variance(AggFunc):
3739    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
3740
3741
3742class VariancePop(AggFunc):
3743    _sql_names = ["VARIANCE_POP", "VAR_POP"]
3744
3745
3746class Week(Func):
3747    arg_types = {"this": True, "mode": False}
3748
3749
3750class XMLTable(Func):
3751    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
3752
3753
3754class Year(Func):
3755    pass
3756
3757
3758class Use(Expression):
3759    arg_types = {"this": True, "kind": False}
3760
3761
3762class Merge(Expression):
3763    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
3764
3765
3766class When(Func):
3767    arg_types = {"this": True, "then": True}
3768
3769
3770def _norm_args(expression):
3771    args = {}
3772
3773    for k, arg in expression.args.items():
3774        if isinstance(arg, list):
3775            arg = [_norm_arg(a) for a in arg]
3776            if not arg:
3777                arg = None
3778        else:
3779            arg = _norm_arg(arg)
3780
3781        if arg is not None and arg is not False:
3782            args[k] = arg
3783
3784    return args
3785
3786
3787def _norm_arg(arg):
3788    return arg.lower() if isinstance(arg, str) else arg
3789
3790
3791ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
3792
3793
3794# Helpers
3795def maybe_parse(
3796    sql_or_expression: str | Expression,
3797    *,
3798    into: t.Optional[IntoType] = None,
3799    dialect: DialectType = None,
3800    prefix: t.Optional[str] = None,
3801    copy: bool = False,
3802    **opts,
3803) -> Expression:
3804    """Gracefully handle a possible string or expression.
3805
3806    Example:
3807        >>> maybe_parse("1")
3808        (LITERAL this: 1, is_string: False)
3809        >>> maybe_parse(to_identifier("x"))
3810        (IDENTIFIER this: x, quoted: False)
3811
3812    Args:
3813        sql_or_expression: the SQL code string or an expression
3814        into: the SQLGlot Expression to parse into
3815        dialect: the dialect used to parse the input expressions (in the case that an
3816            input expression is a SQL string).
3817        prefix: a string to prefix the sql with before it gets parsed
3818            (automatically includes a space)
3819        copy: whether or not to copy the expression.
3820        **opts: other options to use to parse the input expressions (again, in the case
3821            that an input expression is a SQL string).
3822
3823    Returns:
3824        Expression: the parsed or given expression.
3825    """
3826    if isinstance(sql_or_expression, Expression):
3827        if copy:
3828            return sql_or_expression.copy()
3829        return sql_or_expression
3830
3831    import sqlglot
3832
3833    sql = str(sql_or_expression)
3834    if prefix:
3835        sql = f"{prefix} {sql}"
3836    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
3837
3838
3839def _maybe_copy(instance, copy=True):
3840    return instance.copy() if copy else instance
3841
3842
3843def _is_wrong_expression(expression, into):
3844    return isinstance(expression, Expression) and not isinstance(expression, into)
3845
3846
3847def _apply_builder(
3848    expression,
3849    instance,
3850    arg,
3851    copy=True,
3852    prefix=None,
3853    into=None,
3854    dialect=None,
3855    **opts,
3856):
3857    if _is_wrong_expression(expression, into):
3858        expression = into(this=expression)
3859    instance = _maybe_copy(instance, copy)
3860    expression = maybe_parse(
3861        sql_or_expression=expression,
3862        prefix=prefix,
3863        into=into,
3864        dialect=dialect,
3865        **opts,
3866    )
3867    instance.set(arg, expression)
3868    return instance
3869
3870
3871def _apply_child_list_builder(
3872    *expressions,
3873    instance,
3874    arg,
3875    append=True,
3876    copy=True,
3877    prefix=None,
3878    into=None,
3879    dialect=None,
3880    properties=None,
3881    **opts,
3882):
3883    instance = _maybe_copy(instance, copy)
3884    parsed = []
3885    for expression in expressions:
3886        if _is_wrong_expression(expression, into):
3887            expression = into(expressions=[expression])
3888        expression = maybe_parse(
3889            expression,
3890            into=into,
3891            dialect=dialect,
3892            prefix=prefix,
3893            **opts,
3894        )
3895        parsed.extend(expression.expressions)
3896
3897    existing = instance.args.get(arg)
3898    if append and existing:
3899        parsed = existing.expressions + parsed
3900
3901    child = into(expressions=parsed)
3902    for k, v in (properties or {}).items():
3903        child.set(k, v)
3904    instance.set(arg, child)
3905    return instance
3906
3907
3908def _apply_list_builder(
3909    *expressions,
3910    instance,
3911    arg,
3912    append=True,
3913    copy=True,
3914    prefix=None,
3915    into=None,
3916    dialect=None,
3917    **opts,
3918):
3919    inst = _maybe_copy(instance, copy)
3920
3921    expressions = [
3922        maybe_parse(
3923            sql_or_expression=expression,
3924            into=into,
3925            prefix=prefix,
3926            dialect=dialect,
3927            **opts,
3928        )
3929        for expression in expressions
3930    ]
3931
3932    existing_expressions = inst.args.get(arg)
3933    if append and existing_expressions:
3934        expressions = existing_expressions + expressions
3935
3936    inst.set(arg, expressions)
3937    return inst
3938
3939
3940def _apply_conjunction_builder(
3941    *expressions,
3942    instance,
3943    arg,
3944    into=None,
3945    append=True,
3946    copy=True,
3947    dialect=None,
3948    **opts,
3949):
3950    expressions = [exp for exp in expressions if exp is not None and exp != ""]
3951    if not expressions:
3952        return instance
3953
3954    inst = _maybe_copy(instance, copy)
3955
3956    existing = inst.args.get(arg)
3957    if append and existing is not None:
3958        expressions = [existing.this if into else existing] + list(expressions)
3959
3960    node = and_(*expressions, dialect=dialect, **opts)
3961
3962    inst.set(arg, into(this=node) if into else node)
3963    return inst
3964
3965
3966def _combine(expressions, operator, dialect=None, **opts):
3967    expressions = [condition(expression, dialect=dialect, **opts) for expression in expressions]
3968    this = expressions[0]
3969    if expressions[1:]:
3970        this = _wrap_operator(this)
3971    for expression in expressions[1:]:
3972        this = operator(this=this, expression=_wrap_operator(expression))
3973    return this
3974
3975
3976def _wrap_operator(expression):
3977    if isinstance(expression, (And, Or, Not)):
3978        expression = Paren(this=expression)
3979    return expression
3980
3981
3982def union(left, right, distinct=True, dialect=None, **opts):
3983    """
3984    Initializes a syntax tree from one UNION expression.
3985
3986    Example:
3987        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
3988        'SELECT * FROM foo UNION SELECT * FROM bla'
3989
3990    Args:
3991        left (str | Expression): the SQL code string corresponding to the left-hand side.
3992            If an `Expression` instance is passed, it will be used as-is.
3993        right (str | Expression): the SQL code string corresponding to the right-hand side.
3994            If an `Expression` instance is passed, it will be used as-is.
3995        distinct (bool): set the DISTINCT flag if and only if this is true.
3996        dialect (str): the dialect used to parse the input expression.
3997        opts (kwargs): other options to use to parse the input expressions.
3998    Returns:
3999        Union: the syntax tree for the UNION expression.
4000    """
4001    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4002    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4003
4004    return Union(this=left, expression=right, distinct=distinct)
4005
4006
4007def intersect(left, right, distinct=True, dialect=None, **opts):
4008    """
4009    Initializes a syntax tree from one INTERSECT expression.
4010
4011    Example:
4012        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
4013        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
4014
4015    Args:
4016        left (str | Expression): the SQL code string corresponding to the left-hand side.
4017            If an `Expression` instance is passed, it will be used as-is.
4018        right (str | Expression): the SQL code string corresponding to the right-hand side.
4019            If an `Expression` instance is passed, it will be used as-is.
4020        distinct (bool): set the DISTINCT flag if and only if this is true.
4021        dialect (str): the dialect used to parse the input expression.
4022        opts (kwargs): other options to use to parse the input expressions.
4023    Returns:
4024        Intersect: the syntax tree for the INTERSECT expression.
4025    """
4026    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4027    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4028
4029    return Intersect(this=left, expression=right, distinct=distinct)
4030
4031
4032def except_(left, right, distinct=True, dialect=None, **opts):
4033    """
4034    Initializes a syntax tree from one EXCEPT expression.
4035
4036    Example:
4037        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
4038        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
4039
4040    Args:
4041        left (str | Expression): the SQL code string corresponding to the left-hand side.
4042            If an `Expression` instance is passed, it will be used as-is.
4043        right (str | Expression): the SQL code string corresponding to the right-hand side.
4044            If an `Expression` instance is passed, it will be used as-is.
4045        distinct (bool): set the DISTINCT flag if and only if this is true.
4046        dialect (str): the dialect used to parse the input expression.
4047        opts (kwargs): other options to use to parse the input expressions.
4048    Returns:
4049        Except: the syntax tree for the EXCEPT statement.
4050    """
4051    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
4052    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
4053
4054    return Except(this=left, expression=right, distinct=distinct)
4055
4056
4057def select(*expressions: str | Expression, dialect: DialectType = None, **opts) -> Select:
4058    """
4059    Initializes a syntax tree from one or multiple SELECT expressions.
4060
4061    Example:
4062        >>> select("col1", "col2").from_("tbl").sql()
4063        'SELECT col1, col2 FROM tbl'
4064
4065    Args:
4066        *expressions: the SQL code string to parse as the expressions of a
4067            SELECT statement. If an Expression instance is passed, this is used as-is.
4068        dialect: the dialect used to parse the input expressions (in the case that an
4069            input expression is a SQL string).
4070        **opts: other options to use to parse the input expressions (again, in the case
4071            that an input expression is a SQL string).
4072
4073    Returns:
4074        Select: the syntax tree for the SELECT statement.
4075    """
4076    return Select().select(*expressions, dialect=dialect, **opts)
4077
4078
4079def from_(*expressions, dialect=None, **opts) -> Select:
4080    """
4081    Initializes a syntax tree from a FROM expression.
4082
4083    Example:
4084        >>> from_("tbl").select("col1", "col2").sql()
4085        'SELECT col1, col2 FROM tbl'
4086
4087    Args:
4088        *expressions (str | Expression): the SQL code string to parse as the FROM expressions of a
4089            SELECT statement. If an Expression instance is passed, this is used as-is.
4090        dialect (str): the dialect used to parse the input expression (in the case that the
4091            input expression is a SQL string).
4092        **opts: other options to use to parse the input expressions (again, in the case
4093            that the input expression is a SQL string).
4094
4095    Returns:
4096        Select: the syntax tree for the SELECT statement.
4097    """
4098    return Select().from_(*expressions, dialect=dialect, **opts)
4099
4100
4101def update(table, properties, where=None, from_=None, dialect=None, **opts) -> Update:
4102    """
4103    Creates an update statement.
4104
4105    Example:
4106        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
4107        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
4108
4109    Args:
4110        *properties (Dict[str, Any]): dictionary of properties to set which are
4111            auto converted to sql objects eg None -> NULL
4112        where (str): sql conditional parsed into a WHERE statement
4113        from_ (str): sql statement parsed into a FROM statement
4114        dialect (str): the dialect used to parse the input expressions.
4115        **opts: other options to use to parse the input expressions.
4116
4117    Returns:
4118        Update: the syntax tree for the UPDATE statement.
4119    """
4120    update = Update(this=maybe_parse(table, into=Table, dialect=dialect))
4121    update.set(
4122        "expressions",
4123        [
4124            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
4125            for k, v in properties.items()
4126        ],
4127    )
4128    if from_:
4129        update.set(
4130            "from",
4131            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
4132        )
4133    if isinstance(where, Condition):
4134        where = Where(this=where)
4135    if where:
4136        update.set(
4137            "where",
4138            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
4139        )
4140    return update
4141
4142
4143def delete(table, where=None, dialect=None, **opts) -> Delete:
4144    """
4145    Builds a delete statement.
4146
4147    Example:
4148        >>> delete("my_table", where="id > 1").sql()
4149        'DELETE FROM my_table WHERE id > 1'
4150
4151    Args:
4152        where (str|Condition): sql conditional parsed into a WHERE statement
4153        dialect (str): the dialect used to parse the input expressions.
4154        **opts: other options to use to parse the input expressions.
4155
4156    Returns:
4157        Delete: the syntax tree for the DELETE statement.
4158    """
4159    return Delete(
4160        this=maybe_parse(table, into=Table, dialect=dialect, **opts),
4161        where=Where(this=where)
4162        if isinstance(where, Condition)
4163        else maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
4164    )
4165
4166
4167def condition(expression, dialect=None, **opts) -> Condition:
4168    """
4169    Initialize a logical condition expression.
4170
4171    Example:
4172        >>> condition("x=1").sql()
4173        'x = 1'
4174
4175        This is helpful for composing larger logical syntax trees:
4176        >>> where = condition("x=1")
4177        >>> where = where.and_("y=1")
4178        >>> Select().from_("tbl").select("*").where(where).sql()
4179        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
4180
4181    Args:
4182        *expression (str | Expression): the SQL code string to parse.
4183            If an Expression instance is passed, this is used as-is.
4184        dialect (str): the dialect used to parse the input expression (in the case that the
4185            input expression is a SQL string).
4186        **opts: other options to use to parse the input expressions (again, in the case
4187            that the input expression is a SQL string).
4188
4189    Returns:
4190        Condition: the expression
4191    """
4192    return maybe_parse(  # type: ignore
4193        expression,
4194        into=Condition,
4195        dialect=dialect,
4196        **opts,
4197    )
4198
4199
4200def and_(*expressions, dialect=None, **opts) -> And:
4201    """
4202    Combine multiple conditions with an AND logical operator.
4203
4204    Example:
4205        >>> and_("x=1", and_("y=1", "z=1")).sql()
4206        'x = 1 AND (y = 1 AND z = 1)'
4207
4208    Args:
4209        *expressions (str | Expression): the SQL code strings to parse.
4210            If an Expression instance is passed, this is used as-is.
4211        dialect (str): the dialect used to parse the input expression.
4212        **opts: other options to use to parse the input expressions.
4213
4214    Returns:
4215        And: the new condition
4216    """
4217    return _combine(expressions, And, dialect, **opts)
4218
4219
4220def or_(*expressions, dialect=None, **opts) -> Or:
4221    """
4222    Combine multiple conditions with an OR logical operator.
4223
4224    Example:
4225        >>> or_("x=1", or_("y=1", "z=1")).sql()
4226        'x = 1 OR (y = 1 OR z = 1)'
4227
4228    Args:
4229        *expressions (str | Expression): the SQL code strings to parse.
4230            If an Expression instance is passed, this is used as-is.
4231        dialect (str): the dialect used to parse the input expression.
4232        **opts: other options to use to parse the input expressions.
4233
4234    Returns:
4235        Or: the new condition
4236    """
4237    return _combine(expressions, Or, dialect, **opts)
4238
4239
4240def not_(expression, dialect=None, **opts) -> Not:
4241    """
4242    Wrap a condition with a NOT operator.
4243
4244    Example:
4245        >>> not_("this_suit='black'").sql()
4246        "NOT this_suit = 'black'"
4247
4248    Args:
4249        expression (str | Expression): the SQL code strings to parse.
4250            If an Expression instance is passed, this is used as-is.
4251        dialect (str): the dialect used to parse the input expression.
4252        **opts: other options to use to parse the input expressions.
4253
4254    Returns:
4255        Not: the new condition
4256    """
4257    this = condition(
4258        expression,
4259        dialect=dialect,
4260        **opts,
4261    )
4262    return Not(this=_wrap_operator(this))
4263
4264
4265def paren(expression) -> Paren:
4266    return Paren(this=expression)
4267
4268
4269SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
4270
4271
4272@t.overload
4273def to_identifier(name: None, quoted: t.Optional[bool] = None) -> None:
4274    ...
4275
4276
4277@t.overload
4278def to_identifier(name: str | Identifier, quoted: t.Optional[bool] = None) -> Identifier:
4279    ...
4280
4281
4282def to_identifier(name, quoted=None):
4283    """Builds an identifier.
4284
4285    Args:
4286        name: The name to turn into an identifier.
4287        quoted: Whether or not force quote the identifier.
4288
4289    Returns:
4290        The identifier ast node.
4291    """
4292
4293    if name is None:
4294        return None
4295
4296    if isinstance(name, Identifier):
4297        identifier = name
4298    elif isinstance(name, str):
4299        identifier = Identifier(
4300            this=name,
4301            quoted=not re.match(SAFE_IDENTIFIER_RE, name) if quoted is None else quoted,
4302        )
4303    else:
4304        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
4305    return identifier
4306
4307
4308INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
4309
4310
4311def to_interval(interval: str | Literal) -> Interval:
4312    """Builds an interval expression from a string like '1 day' or '5 months'."""
4313    if isinstance(interval, Literal):
4314        if not interval.is_string:
4315            raise ValueError("Invalid interval string.")
4316
4317        interval = interval.this
4318
4319    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
4320
4321    if not interval_parts:
4322        raise ValueError("Invalid interval string.")
4323
4324    return Interval(
4325        this=Literal.string(interval_parts.group(1)),
4326        unit=Var(this=interval_parts.group(2)),
4327    )
4328
4329
4330@t.overload
4331def to_table(sql_path: str | Table, **kwargs) -> Table:
4332    ...
4333
4334
4335@t.overload
4336def to_table(sql_path: None, **kwargs) -> None:
4337    ...
4338
4339
4340def to_table(sql_path: t.Optional[str | Table], **kwargs) -> t.Optional[Table]:
4341    """
4342    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
4343    If a table is passed in then that table is returned.
4344
4345    Args:
4346        sql_path: a `[catalog].[schema].[table]` string.
4347
4348    Returns:
4349        A table expression.
4350    """
4351    if sql_path is None or isinstance(sql_path, Table):
4352        return sql_path
4353    if not isinstance(sql_path, str):
4354        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
4355
4356    catalog, db, table_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 3))
4357    return Table(this=table_name, db=db, catalog=catalog, **kwargs)
4358
4359
4360def to_column(sql_path: str | Column, **kwargs) -> Column:
4361    """
4362    Create a column from a `[table].[column]` sql path. Schema is optional.
4363
4364    If a column is passed in then that column is returned.
4365
4366    Args:
4367        sql_path: `[table].[column]` string
4368    Returns:
4369        Table: A column expression
4370    """
4371    if sql_path is None or isinstance(sql_path, Column):
4372        return sql_path
4373    if not isinstance(sql_path, str):
4374        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
4375    table_name, column_name = (to_identifier(x) for x in split_num_words(sql_path, ".", 2))
4376    return Column(this=column_name, table=table_name, **kwargs)
4377
4378
4379def alias_(
4380    expression: str | Expression,
4381    alias: str | Identifier,
4382    table: bool | t.Sequence[str | Identifier] = False,
4383    quoted: t.Optional[bool] = None,
4384    dialect: DialectType = None,
4385    **opts,
4386):
4387    """Create an Alias expression.
4388
4389    Example:
4390        >>> alias_('foo', 'bar').sql()
4391        'foo AS bar'
4392
4393        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
4394        '(SELECT 1, 2) AS bar(a, b)'
4395
4396    Args:
4397        expression: the SQL code strings to parse.
4398            If an Expression instance is passed, this is used as-is.
4399        alias: the alias name to use. If the name has
4400            special characters it is quoted.
4401        table: Whether or not to create a table alias, can also be a list of columns.
4402        quoted: whether or not to quote the alias
4403        dialect: the dialect used to parse the input expression.
4404        **opts: other options to use to parse the input expressions.
4405
4406    Returns:
4407        Alias: the aliased expression
4408    """
4409    exp = maybe_parse(expression, dialect=dialect, **opts)
4410    alias = to_identifier(alias, quoted=quoted)
4411
4412    if table:
4413        table_alias = TableAlias(this=alias)
4414        exp.set("alias", table_alias)
4415
4416        if not isinstance(table, bool):
4417            for column in table:
4418                table_alias.append("columns", to_identifier(column, quoted=quoted))
4419
4420        return exp
4421
4422    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
4423    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
4424    # for the complete Window expression.
4425    #
4426    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
4427
4428    if "alias" in exp.arg_types and not isinstance(exp, Window):
4429        exp = exp.copy()
4430        exp.set("alias", alias)
4431        return exp
4432    return Alias(this=exp, alias=alias)
4433
4434
4435def subquery(expression, alias=None, dialect=None, **opts):
4436    """
4437    Build a subquery expression.
4438
4439    Example:
4440        >>> subquery('select x from tbl', 'bar').select('x').sql()
4441        'SELECT x FROM (SELECT x FROM tbl) AS bar'
4442
4443    Args:
4444        expression (str | Expression): the SQL code strings to parse.
4445            If an Expression instance is passed, this is used as-is.
4446        alias (str | Expression): the alias name to use.
4447        dialect (str): the dialect used to parse the input expression.
4448        **opts: other options to use to parse the input expressions.
4449
4450    Returns:
4451        Select: a new select with the subquery expression included
4452    """
4453
4454    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
4455    return Select().from_(expression, dialect=dialect, **opts)
4456
4457
4458def column(
4459    col: str | Identifier,
4460    table: t.Optional[str | Identifier] = None,
4461    schema: t.Optional[str | Identifier] = None,
4462    quoted: t.Optional[bool] = None,
4463) -> Column:
4464    """
4465    Build a Column.
4466
4467    Args:
4468        col: column name
4469        table: table name
4470        schema: schema name
4471        quoted: whether or not to force quote each part
4472    Returns:
4473        Column: column instance
4474    """
4475    return Column(
4476        this=to_identifier(col, quoted=quoted),
4477        table=to_identifier(table, quoted=quoted),
4478        schema=to_identifier(schema, quoted=quoted),
4479    )
4480
4481
4482def cast(expression: str | Expression, to: str | DataType | DataType.Type, **opts) -> Cast:
4483    """Cast an expression to a data type.
4484
4485    Example:
4486        >>> cast('x + 1', 'int').sql()
4487        'CAST(x + 1 AS INT)'
4488
4489    Args:
4490        expression: The expression to cast.
4491        to: The datatype to cast to.
4492
4493    Returns:
4494        A cast node.
4495    """
4496    expression = maybe_parse(expression, **opts)
4497    return Cast(this=expression, to=DataType.build(to, **opts))
4498
4499
4500def table_(table, db=None, catalog=None, quoted=None, alias=None) -> Table:
4501    """Build a Table.
4502
4503    Args:
4504        table (str | Expression): column name
4505        db (str | Expression): db name
4506        catalog (str | Expression): catalog name
4507
4508    Returns:
4509        Table: table instance
4510    """
4511    return Table(
4512        this=to_identifier(table, quoted=quoted),
4513        db=to_identifier(db, quoted=quoted),
4514        catalog=to_identifier(catalog, quoted=quoted),
4515        alias=TableAlias(this=to_identifier(alias)) if alias else None,
4516    )
4517
4518
4519def values(
4520    values: t.Iterable[t.Tuple[t.Any, ...]],
4521    alias: t.Optional[str] = None,
4522    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
4523) -> Values:
4524    """Build VALUES statement.
4525
4526    Example:
4527        >>> values([(1, '2')]).sql()
4528        "VALUES (1, '2')"
4529
4530    Args:
4531        values: values statements that will be converted to SQL
4532        alias: optional alias
4533        columns: Optional list of ordered column names or ordered dictionary of column names to types.
4534         If either are provided then an alias is also required.
4535         If a dictionary is provided then the first column of the values will be casted to the expected type
4536         in order to help with type inference.
4537
4538    Returns:
4539        Values: the Values expression object
4540    """
4541    if columns and not alias:
4542        raise ValueError("Alias is required when providing columns")
4543    table_alias = (
4544        TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
4545        if columns
4546        else TableAlias(this=to_identifier(alias) if alias else None)
4547    )
4548    expressions = [convert(tup) for tup in values]
4549    if columns and isinstance(columns, dict):
4550        types = list(columns.values())
4551        expressions[0].set(
4552            "expressions",
4553            [cast(x, types[i]) for i, x in enumerate(expressions[0].expressions)],
4554        )
4555    return Values(
4556        expressions=expressions,
4557        alias=table_alias,
4558    )
4559
4560
4561def var(name: t.Optional[str | Expression]) -> Var:
4562    """Build a SQL variable.
4563
4564    Example:
4565        >>> repr(var('x'))
4566        '(VAR this: x)'
4567
4568        >>> repr(var(column('x', table='y')))
4569        '(VAR this: x)'
4570
4571    Args:
4572        name: The name of the var or an expression who's name will become the var.
4573
4574    Returns:
4575        The new variable node.
4576    """
4577    if not name:
4578        raise ValueError(f"Cannot convert empty name into var.")
4579
4580    if isinstance(name, Expression):
4581        name = name.name
4582    return Var(this=name)
4583
4584
4585def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
4586    """Build ALTER TABLE... RENAME... expression
4587
4588    Args:
4589        old_name: The old name of the table
4590        new_name: The new name of the table
4591
4592    Returns:
4593        Alter table expression
4594    """
4595    old_table = to_table(old_name)
4596    new_table = to_table(new_name)
4597    return AlterTable(
4598        this=old_table,
4599        actions=[
4600            RenameTable(this=new_table),
4601        ],
4602    )
4603
4604
4605def convert(value) -> Expression:
4606    """Convert a python value into an expression object.
4607
4608    Raises an error if a conversion is not possible.
4609
4610    Args:
4611        value (Any): a python object
4612
4613    Returns:
4614        Expression: the equivalent expression object
4615    """
4616    if isinstance(value, Expression):
4617        return value
4618    if value is None:
4619        return NULL
4620    if isinstance(value, bool):
4621        return Boolean(this=value)
4622    if isinstance(value, str):
4623        return Literal.string(value)
4624    if isinstance(value, float) and math.isnan(value):
4625        return NULL
4626    if isinstance(value, numbers.Number):
4627        return Literal.number(value)
4628    if isinstance(value, tuple):
4629        return Tuple(expressions=[convert(v) for v in value])
4630    if isinstance(value, list):
4631        return Array(expressions=[convert(v) for v in value])
4632    if isinstance(value, dict):
4633        return Map(
4634            keys=[convert(k) for k in value],
4635            values=[convert(v) for v in value.values()],
4636        )
4637    if isinstance(value, datetime.datetime):
4638        datetime_literal = Literal.string(
4639            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
4640        )
4641        return TimeStrToTime(this=datetime_literal)
4642    if isinstance(value, datetime.date):
4643        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
4644        return DateStrToDate(this=date_literal)
4645    raise ValueError(f"Cannot convert {value}")
4646
4647
4648def replace_children(expression, fun):
4649    """
4650    Replace children of an expression with the result of a lambda fun(child) -> exp.
4651    """
4652    for k, v in expression.args.items():
4653        is_list_arg = isinstance(v, list)
4654
4655        child_nodes = v if is_list_arg else [v]
4656        new_child_nodes = []
4657
4658        for cn in child_nodes:
4659            if isinstance(cn, Expression):
4660                for child_node in ensure_collection(fun(cn)):
4661                    new_child_nodes.append(child_node)
4662                    child_node.parent = expression
4663                    child_node.arg_key = k
4664            else:
4665                new_child_nodes.append(cn)
4666
4667        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
4668
4669
4670def column_table_names(expression):
4671    """
4672    Return all table names referenced through columns in an expression.
4673
4674    Example:
4675        >>> import sqlglot
4676        >>> column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e"))
4677        ['c', 'a']
4678
4679    Args:
4680        expression (sqlglot.Expression): expression to find table names
4681
4682    Returns:
4683        list: A list of unique names
4684    """
4685    return list(dict.fromkeys(column.table for column in expression.find_all(Column)))
4686
4687
4688def table_name(table) -> str:
4689    """Get the full name of a table as a string.
4690
4691    Args:
4692        table (exp.Table | str): table expression node or string.
4693
4694    Examples:
4695        >>> from sqlglot import exp, parse_one
4696        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
4697        'a.b.c'
4698
4699    Returns:
4700        The table name.
4701    """
4702
4703    table = maybe_parse(table, into=Table)
4704
4705    if not table:
4706        raise ValueError(f"Cannot parse {table}")
4707
4708    return ".".join(
4709        part
4710        for part in (
4711            table.text("catalog"),
4712            table.text("db"),
4713            table.name,
4714        )
4715        if part
4716    )
4717
4718
4719def replace_tables(expression, mapping):
4720    """Replace all tables in expression according to the mapping.
4721
4722    Args:
4723        expression (sqlglot.Expression): expression node to be transformed and replaced.
4724        mapping (Dict[str, str]): mapping of table names.
4725
4726    Examples:
4727        >>> from sqlglot import exp, parse_one
4728        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
4729        'SELECT * FROM c'
4730
4731    Returns:
4732        The mapped expression.
4733    """
4734
4735    def _replace_tables(node):
4736        if isinstance(node, Table):
4737            new_name = mapping.get(table_name(node))
4738            if new_name:
4739                return to_table(
4740                    new_name,
4741                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
4742                )
4743        return node
4744
4745    return expression.transform(_replace_tables)
4746
4747
4748def replace_placeholders(expression, *args, **kwargs):
4749    """Replace placeholders in an expression.
4750
4751    Args:
4752        expression (sqlglot.Expression): expression node to be transformed and replaced.
4753        args: positional names that will substitute unnamed placeholders in the given order.
4754        kwargs: keyword arguments that will substitute named placeholders.
4755
4756    Examples:
4757        >>> from sqlglot import exp, parse_one
4758        >>> replace_placeholders(
4759        ...     parse_one("select * from :tbl where ? = ?"), "a", "b", tbl="foo"
4760        ... ).sql()
4761        'SELECT * FROM foo WHERE a = b'
4762
4763    Returns:
4764        The mapped expression.
4765    """
4766
4767    def _replace_placeholders(node, args, **kwargs):
4768        if isinstance(node, Placeholder):
4769            if node.name:
4770                new_name = kwargs.get(node.name)
4771                if new_name:
4772                    return to_identifier(new_name)
4773            else:
4774                try:
4775                    return to_identifier(next(args))
4776                except StopIteration:
4777                    pass
4778        return node
4779
4780    return expression.transform(_replace_placeholders, iter(args), **kwargs)
4781
4782
4783def expand(expression: Expression, sources: t.Dict[str, Subqueryable], copy=True) -> Expression:
4784    """Transforms an expression by expanding all referenced sources into subqueries.
4785
4786    Examples:
4787        >>> from sqlglot import parse_one
4788        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
4789        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
4790
4791    Args:
4792        expression: The expression to expand.
4793        sources: A dictionary of name to Subqueryables.
4794        copy: Whether or not to copy the expression during transformation. Defaults to True.
4795
4796    Returns:
4797        The transformed expression.
4798    """
4799
4800    def _expand(node: Expression):
4801        if isinstance(node, Table):
4802            name = table_name(node)
4803            source = sources.get(name)
4804            if source:
4805                subquery = source.subquery(node.alias or name)
4806                subquery.comments = [f"source: {name}"]
4807                return subquery
4808        return node
4809
4810    return expression.transform(_expand, copy=copy)
4811
4812
4813def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
4814    """
4815    Returns a Func expression.
4816
4817    Examples:
4818        >>> func("abs", 5).sql()
4819        'ABS(5)'
4820
4821        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
4822        'CAST(5 AS DOUBLE)'
4823
4824    Args:
4825        name: the name of the function to build.
4826        args: the args used to instantiate the function of interest.
4827        dialect: the source dialect.
4828        kwargs: the kwargs used to instantiate the function of interest.
4829
4830    Note:
4831        The arguments `args` and `kwargs` are mutually exclusive.
4832
4833    Returns:
4834        An instance of the function of interest, or an anonymous function, if `name` doesn't
4835        correspond to an existing `sqlglot.expressions.Func` class.
4836    """
4837    if args and kwargs:
4838        raise ValueError("Can't use both args and kwargs to instantiate a function.")
4839
4840    from sqlglot.dialects.dialect import Dialect
4841
4842    args = tuple(convert(arg) for arg in args)
4843    kwargs = {key: convert(value) for key, value in kwargs.items()}
4844
4845    parser = Dialect.get_or_raise(dialect)().parser()
4846    from_args_list = parser.FUNCTIONS.get(name.upper())
4847
4848    if from_args_list:
4849        function = from_args_list(args) if args else from_args_list.__self__(**kwargs)  # type: ignore
4850    else:
4851        kwargs = kwargs or {"expressions": args}
4852        function = Anonymous(this=name, **kwargs)
4853
4854    for error_message in function.error_messages(args):
4855        raise ValueError(error_message)
4856
4857    return function
4858
4859
4860def true():
4861    """
4862    Returns a true Boolean expression.
4863    """
4864    return Boolean(this=True)
4865
4866
4867def false():
4868    """
4869    Returns a false Boolean expression.
4870    """
4871    return Boolean(this=False)
4872
4873
4874def null():
4875    """
4876    Returns a Null expression.
4877    """
4878    return Null()
4879
4880
4881# TODO: deprecate this
4882TRUE = Boolean(this=True)
4883FALSE = Boolean(this=False)
4884NULL = Null()